select ywlxc_id,ywlxc_name,isnull((select sum(ry_8y) from ry where ry_yytid=ywlxc_id),0) as yrw from ywlxc where ywlxc_del = 0 and yrw >80我可以将 yrw 作为条件判断吗,为什么只要加上yrw大于80就会报错,不能用汇总结果作为条件吗?还是我写错了?
调试欢乐多
select * from (select ywlxc_id,ywlxc_name,isnull((select sum(ry_8y) from ry where ry_yytid=ywlxc_id),0) as yrw from ywlxc ) a where ywlxc_del = 0 and yrw >80
from ywlxc
where ywlxc_del = 0 and isnull((select sum(ry_8y) from ry where ry_yytid=ywlxc_id),0) >80
isnull((select sum(ry_8y) from ry
where ry_yytid=ywlxc_id and yrw >80),0) as yrw --放到子查询里
from ywlxc where ywlxc_del = 0
select *
from(
select ywlxc_id,ywlxc_name,isnull((select sum(ry_8y) from ry where ry_yytid=ywlxc_id),0) as yrw
from ywlxc
where ywlxc_del = 0
)t
where yrw > 80
--修改
select * from
(select ywlxc_id,ywlxc_name,
isnull((select sum(ry_8y) from ry where ry_yytid=ywlxc_id),0) as yrw
from ywlxc where ywlxc_del = 0) t where yrw>80
1、FROM
2、ON
3、JOIN--做笛卡尔乘积
4、WHERE
5、GROUP BY
6、WITH(cube | rollup )
7、HAVING
8、SELECT 列表
9、DISTINCT
10、ORDER BY
WHERE在SELECT前执行,因此你SELECT后面的别名不能在WHERE子句中使用,因为这时候该别名都还没生成。
*
from
(select ywlxc_id,ywlxc_name,isnull((select sum(ry_8y) from ry where ry_yytid=ywlxc_id),0) as yrw from ywlxc where ywlxc_del = 0)t
where
yrw >80