问个sql语句错误问题，解决马上结贴感谢

[kmhugh]

select DISTINCT(t.cjgdm),t.cjgmc,(select sum(czbs) from contest_jghz where cjgmc=t.cjgmc) as hz
from contest_jghz t inner join contest_jg a on a.cjgdm=t.cjgdm where a.cjglb <> '2' and a.cjglb <> '6' and t.hz>'1000'

错误提示“列名 'hz' 无效”

如果不要后面的“and t.hz>'1000'”条件运行正确

感谢！

[水族杰纶]

select DISTINCT(t.cjgdm),t.cjgmc,(select sum(czbs) from contest_jghz where cjgmc=t.cjgmc) as hz 
from contest_jghz t inner join contest_jg a on a.cjgdm=t.cjgdm and t.hz>'1000'
where a.cjglb <> '2' and a.cjglb <> '6'  

[Yang_]

select DISTINCT(t.cjgdm),t.cjgmc,(select sum(czbs) from contest_jghz where cjgmc=t.cjgmc) as hz 
from contest_jghz t inner join contest_jg a on a.cjgdm=t.cjgdm 
where a.cjglb <> '2' and a.cjglb <> '6' and (select sum(czbs) from contest_jghz where cjgmc=t.cjgmc)>'1000' 

[--小F--]

--try
select 
  DISTINCT(t.cjgdm),t.cjgmc,
  (select sum(czbs) as t from contest_jghz where cjgmc=t.cjgmc) as hz 
from 
  contest_jghz t 
inner join 
  contest_jg a on a.cjgdm=t.cjgdm where a.cjglb <> '2' and a.cjglb <> '6' and hz.t>'1000' 

[kmhugh]

给个理由就更感谢了，感谢~

[--小F--]

--或者直接这样
select 
  DISTINCT(t.cjgdm),t.cjgmc,
  (select sum(czbs) from contest_jghz where cjgmc=t.cjgmc) as hz 
from 
   contest_jghz t inner join contest_jg a on a.cjgdm=t.cjgdm 
where 
  a.cjglb <> '2' and a.cjglb <> '6' 
and 
  (select sum(czbs) from contest_jghz where cjgmc=t.cjgmc)>'1000'

[Yang_]

2005+:

select DISTINCT(t.cjgdm),t.cjgmc,x.hz 
from contest_jghz t inner join contest_jg a on a.cjgdm=t.cjgdm 
cross apply (select sum(czbs) as hz from contest_jghz where cjgmc=t.cjgmc) as x
where a.cjglb <> '2' and a.cjglb <> '6' 
and x.hz>'1000' 

[alisafan123]

and(select sum(czbs) from contest_jghz where cjgmc=t.cjgmc)>'1000'

[--小F--]

[Quote=引用 4 楼 kmhugh 的回复:]
给个理由就更感谢了，感谢~
[/Quote]

你前面的是给表取了个别名 在后面比较大小的时候应该取hz表里面的sum(czbs)这个字段

[闲思暇想]

理由就是(select sum(czbs) from contest_jghz where cjgmc=t.cjgmc) as hz 。
你这里hz是别名，是计算出来的一列，不是实体列，也不属于那个表t的列。所以你最后的t.hz就让电脑懵了，其实我也有点懵。