关于分组语句,还能进一步吗,有这样的SQL可以做到吗

[chuting1]

我用分组语句得到一个结果

select priceName,operator,sum(amount) as sl1,sum(money) as money1 from bussinessrecords group by operator,priceName

priname operator sl1 money1
居民 aa 20 40
经营 aa 15 45
工业 aa 100 1000
居民 bb 30 60
经营 bb 12 36

能不能有这样一个语句,得到如下结果

居民 aa 20 40
经营 aa 15 45
工业 aa 100 1000
全部 aa 135 1085
居民 bb 30 60
经营 bb 12 36
全部 bb 42 96

[chuting1]

select b.priceName,a.operator,sum(a.amount) as sl1,sum(a.money) as money1,sum(a.wushui) as wushui1 from bussinessrecords a,priceinfo b where b.priceSN=a.property group by a.operator,b.priceName


我原来是这样的,照上面的修改后,总提示别名的问题

select b.priceName,a.operator,sum(a.amount) as sl1,sum(a.money) as money1 from
(
(
select b.priceName,a.operator,a.amount,a.money from bussinessrecords a,priceinfo b where a.property=b.pricesn
)
union all
(
select '全部' as b.priceName,d.operator,sum(d.amount) as a.amount,sum(d.money) as a.money from bussinessrecords d,priceinfo d group by d.operator
)
) c
group by a.operator,b.priceName

有什么不对的吗

[jhone99]

SELECT priceName
,operator
,sl1
,money1
,wushui1
FROM (
select b.priceName
,a.operator
,sum(a.amount) as sl1
,sum(a.money) as money1
,sum(a.wushui) as wushui1
from bussinessrecords a
,priceinfo b
where b.priceSN=a.property
group by a.operator,b.priceName
UNION ALL
select '全部'
,a.operator
,sum(a.amount)
,sum(a.money)
,sum(a.wushui)
from bussinessrecords a
,priceinfo b
where b.priceSN=a.property
group by a.operator
)
ORDER BY operator,priceName

[chuting1]

如果已有
priname operator sl1 money1
居民 aa 20 40
经营 aa 15 45
工业 aa 100 1000
居民 bb 30 60
经营 bb 12 36

能不能有这样一个语句,得到如下结果

居民 aa 20 40
经营 aa 15 45
工业 aa 100 1000
全部 aa 135 1085
居民 bb 30 60
经营 bb 12 36
全部 bb 42 96


这样为什么提示 join 操作语法错误呢

[chuting1]

SELECT a1 ,a2,a3,a4,a5 From ((select * from accesstemp) union all (select '全部' as a1,a2,sum(a3) as a3,sum(a4) as a4,sum(a5) as a5 from accesstemp group by a2)) a group by a2,a1


a1,a2,a3,a4,a5已简写,为什么提示 join 操作语法错误呢

[jhone99]

[Quote=引用 6 楼 chuting 的回复:]
SELECT a1 ,a2,a3,a4,a5 From ((select * from accesstemp) union all (select '全部' as a1,a2,sum(a3) as a3,sum(a4) as a4,sum(a5) as a5 from accesstemp group by a2)) a group by a2,a1


a1,a2,a3,a4,a5已简写,为什么提示 join 操作语法错误呢
[/Quote]



你的写法问题很多

* 和
'全部' as a1,a2,sum(a3) as a3,sum(a4) as a4,sum(a5) as a5
需要对应

用简写有时候是看不出问题的

[chuting1]

SELECT a1,a2,a33,a44,a55 From ((select a1,a2,a3 as a33,a4 as a44,a5 as a55 from accesstemp)a union all(select '全部' as a1,a2,sum(a3) as a33,sum(a4) as a44,sum(a5) as a55 from accesstemp group by a2) b)c group by a2,a1

这样都不行了?

[jhone99]

问题很多
按我的做

[chuting1]

谢谢你们的提示

(select a1,a2,a3 as a33,a4 as a44,a5 as a55 from accesstemp) UNION ALL (select a1, '总计l' as a2,sum(a3) as a33,sum(a4) as a44,sum(a5) as a55 from accesstemp group by a1)
ORDER BY a1, a2

OK

[feiyun0112]

select priceName,operator,sum(amount) as sl1,sum(money) as money1 from
(
(
select priceName,operator,amount,money from bussinessrecords
)
union all
(
select '全部' as priceName,operator,sum(amount) as amount,sum(money) as money from bussinessrecords group by operator
)
) a
group by operator,priceName

*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码)
http://feiyun0112.cnblogs.com/

[jhone99]

select priceName
,operator
,sl1
,money1
from (
select priceName
,operator
,sum(amount) as sl1
,sum(money) as money1
from bussinessrecords
group by operator,priceName
union all
select '全部' as priceName
,operator
,sum(amount) as sl1
,sum(money) as money1
from bussinessrecords
group by operator
)
order by priceName,operator