Question

SQL语句 别名为啥无效，怎么解决啊

select a.gsName, a.quName,
sum(a.num) 民用水量,
(select sum(price) from a where typeId=1) 水费, (select sum(price) from a where typeId=2) 违约金,
(select sum(price) from a where typeId=3) 维修费, (select sum(price) from a where typeId=4) 维护费,
sum(b.num) 商用水量,
(select sum(price) from b where typeId=1) 水费, (select sum(price) from b where typeId=2) 违约金,
(select sum(price) from b where typeId=3) 维修费, (select sum(price) from b where typeId=4) 维护费
from (select gsName, quName, num, price, typeId from mingxi, users, fenqu where fenqu.gsId=users.gsId and mingxi.userId=users.userId and xzId=1) as a,
(select gsName, quName, num, price, typeId from mingxi, users, fenqu where fenqu.gsId=users.gsId and mingxi.userId=users.userId and xzId=2) as b
group by a.gsName, a.quName
go错误提示：消息 208，级别 16，状态 1，第 1 行
对象名 'a' 无效。

Answer 1

老大，你这个好复杂哟，呵呵。。。(select sum(price) from a where typeId=1) 水费, (select sum(price) from a where typeId=2) 像这个语句里面，作用域不一样了，所以写别名他找不到了。建议你把(select gsName, quName, num, price, typeId from mingxi, users, fenqu where fenqu.gsId=users.gsId and mingxi.userId=users.userId and xzId=1)和(select gsName, quName, num, price, typeId from mingxi, users, fenqu where fenqu.gsId=users.gsId and mingxi.userId=users.userId and xzId=2)写成视图，然后这样用： select a.gsName, a.quName,
sum(a.num) 民用水量,
(select sum(price) from 你的视图a where typeId=1) 水费, (select sum(price) from 你的视图a where typeId=2) 违约金,
(select sum(price) from 你的视图a where typeId=3) 维修费, (select sum(price) from 你的视图a where typeId=4) 维护费,
sum(b.num) 商用水量,
(select sum(price) from 你的视图b where typeId=1) 水费, (select sum(price) from 你的视图b where typeId=2) 违约金,
(select sum(price) from 你的视图b where typeId=3) 维修费, (select sum(price) from 你的视图b where typeId=4) 维护费
from 你的视图a as a,
你的视图b as b
group by a.gsName, a.quName
go

Answer 2

采用ＬＩＮＱ的写法吧，把from那部分写到试试