union all和group by怎么结合？

foxman 2004-12-21 09:43:47
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,sum(kcsw_rksl)-sum(kcsw_cksl) as sqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as sqjcdw, 0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqrkdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <'20041219' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj,0 as sqjcsl,0 as sqjcdw,sum(kcsw_rksl) as bqrksl,sum(kcsw_rkdw) as bqrkdw ,sum(kcsw_cksl) as bqcksl,sum(kcsw_ckdw) as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq between '20041220' and '20041220'
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,0 as sqjcsl,0 as sqjcdw,0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw ,sum(kcsw_rksl)-sum(kcsw_cksl) as bqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <='20041220' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj


如果对以上的记录再按wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj 进行汇总，语句该怎么写啊？
...全文
1479 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
alipaymate 2005-02-25
  • 打赏
  • 举报
 回复
up
foxman 2004-12-29
  • 打赏
  • 举报
 回复
问题解决。结帖给分
zhangzs8896 2004-12-21
  • 打赏
  • 举报
 回复
应该是没问题了吧，按你的要求，应该你最后写的可以得到正确答案了。
羊羽亻子 2004-12-21
  • 打赏
  • 举报
 回复
就是这样的了，没报错就行
foxman 2004-12-21
  • 打赏
  • 举报
 回复
谢谢各位！
这样写是否正确了？
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj,sum(sqjcsl),sum(sqjcdw),sum(bqrksl),sum(bqrkdw),sum(bqcksl),
sum(bqckdw),sum(bqjcsl),sum(bqjcdw) from
(
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,sum(kcsw_rksl)-sum(kcsw_cksl) as sqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as sqjcdw, 0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <'20041219' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj,0 as sqjcsl,0 as sqjcdw,sum(kcsw_rksl) as bqrksl,sum(kcsw_rkdw) as bqrkdw ,sum(kcsw_cksl) as bqcksl,sum(kcsw_ckdw) as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq between '20041220' and '20041220'
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,0 as sqjcsl,0 as sqjcdw,0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw ,sum(kcsw_rksl)-sum(kcsw_cksl) as bqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <='20041220' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
) a
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
foxman 2004-12-21
  • 打赏
  • 举报
 回复
第一次写错了，出现了两次
正确的原文应该是：

select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,sum(kcsw_rksl)-sum(kcsw_cksl) as sqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as sqjcdw, 0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <'20041219' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj,0 as sqjcsl,0 as sqjcdw,sum(kcsw_rksl) as bqrksl,sum(kcsw_rkdw) as bqrkdw ,sum(kcsw_cksl) as bqcksl,sum(kcsw_ckdw) as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq between '20041220' and '20041220'
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,0 as sqjcsl,0 as sqjcdw,0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw ,sum(kcsw_rksl)-sum(kcsw_cksl) as bqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <='20041220' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
zhangzs8896 2004-12-21
  • 打赏
  • 举报
 回复
我想楼主似乎没对group by 整明白。
如楼上：
wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj 进行分组，那么你select字段中除了这些字段外的其他字段必须要写在聚合函数中的。
zhangzs8896 2004-12-21
  • 打赏
  • 举报
 回复
Server: Msg 8156, Level 16, State 1, Line 1
The column 'bqrkdw' was specified multiple times for 'A'.
===
你这个字段出现多次吗？

类似：
create table tb1(a int,b varchar(2))
insert into tb1
select 10,'01' union all
select 20, '01' union all
select 30,'02'
create table tb2 (a int,b varchar(2))
insert into tb2
select 40,'01' union all
select 50, '02' union all
select 60 ,'03'

select a=sum(a),b from (
select a,b from tb1
union all
select a,b from tb2)a
group by b
drop table tb1,tb2

===
a b
----------- ----
70 01
80 02
60 03
foxman 2004-12-21
  • 打赏
  • 举报
 回复
原来的语句应该是：
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,sum(kcsw_rksl)-sum(kcsw_cksl) as sqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as sqjcdw, 0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <'20041219' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj,0 as sqjcsl,0 as sqjcdw,sum(kcsw_rksl) as bqrksl,sum(kcsw_rkdw) as bqrkdw ,sum(kcsw_cksl) as bqcksl,sum(kcsw_ckdw) as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq between '20041220' and '20041220'
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,0 as sqjcsl,0 as sqjcdw,0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw ,sum(kcsw_rksl)-sum(kcsw_cksl) as bqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <='20041220' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj


写成：
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj,sqjcsl,sqjcdw,bqrksl,bqrkdw,bqcksl,bqckdw,bqjcsl,bqjcdw from
(
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,sum(kcsw_rksl)-sum(kcsw_cksl) as sqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as sqjcdw, 0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <'20041219' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj,0 as sqjcsl,0 as sqjcdw,sum(kcsw_rksl) as bqrksl,sum(kcsw_rkdw) as bqrkdw ,sum(kcsw_cksl) as bqcksl,sum(kcsw_ckdw) as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq between '20041220' and '20041220'
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,0 as sqjcsl,0 as sqjcdw,0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw ,sum(kcsw_rksl)-sum(kcsw_cksl) as bqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <='20041220' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
) a
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj


Server: Msg 8120, Level 16, State 1, Line 1
Column 'a.sqjcsl' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'a.sqjcdw' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'a.bqrksl' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'a.bqrkdw' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'a.bqcksl' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'a.bqckdw' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'a.bqjcsl' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'a.bqjcdw' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


zhangzs8896 2004-12-21
  • 打赏
  • 举报
 回复
晕，没写全。
跟楼上基本一致
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
sum(..),max(..),..
from
(
你原由的sql语句
)a
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
foxman 2004-12-21
  • 打赏
  • 举报
 回复
select wl_wlid ,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,sqjcsl,sqjcdw,bqrksl,bqrkdw,bqcksl,bqckdw,bqjcsl,bqjcdw From
(select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,sum(kcsw_rksl)-sum(kcsw_cksl) as sqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as sqjcdw, 0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqrkdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <'20041219' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj,0 as sqjcsl,0 as sqjcdw,sum(kcsw_rksl) as bqrksl,sum(kcsw_rkdw) as bqrkdw ,sum(kcsw_cksl) as bqcksl,sum(kcsw_ckdw) as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq between '20041220' and '20041220'
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,0 as sqjcsl,0 as sqjcdw,0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw ,sum(kcsw_rksl)-sum(kcsw_cksl) as bqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <='20041220' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
)A
group by wl_wlid ,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj


Server: Msg 8156, Level 16, State 1, Line 1
The column 'bqrkdw' was specified multiple times for 'A'.
long0104 2004-12-21
  • 打赏
  • 举报
 回复
select * from
(select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,sum(kcsw_rksl)-sum(kcsw_cksl) as sqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as sqjcdw, 0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqrkdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <'20041219' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj) a
union all
select * from
(select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj,0 as sqjcsl,0 as sqjcdw,sum(kcsw_rksl) as bqrksl,sum(kcsw_rkdw) as bqrkdw ,sum(kcsw_cksl) as bqcksl,sum(kcsw_ckdw) as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq between '20041220' and '20041220'
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj) b
union all
select * from
(select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,0 as sqjcsl,0 as sqjcdw,0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw ,sum(kcsw_rksl)-sum(kcsw_cksl) as bqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <='20041220' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj) c
子陌红尘 2004-12-21
  • 打赏
  • 举报
 回复
select * from
(select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,sum(kcsw_rksl)-sum(kcsw_cksl) as sqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as sqjcdw, 0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqrkdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <'20041219' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj) a
union all
select * from
(select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj,0 as sqjcsl,0 as sqjcdw,sum(kcsw_rksl) as bqrksl,sum(kcsw_rkdw) as bqrkdw ,sum(kcsw_cksl) as bqcksl,sum(kcsw_ckdw) as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq between '20041220' and '20041220'
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj) b
union all
select * from
(select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,0 as sqjcsl,0 as sqjcdw,0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw ,sum(kcsw_rksl)-sum(kcsw_cksl) as bqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <='20041220' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj) c
LBYYBL 2004-12-21
  • 打赏
  • 举报
 回复
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj from
(
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj from t1
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj from t2
)a
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
didoleo 2004-12-21
  • 打赏
  • 举报
 回复
select group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj,sum().... from

(
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,sum(kcsw_rksl)-sum(kcsw_cksl) as sqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as sqjcdw, 0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqrkdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <'20041219' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj,0 as sqjcsl,0 as sqjcdw,sum(kcsw_rksl) as bqrksl,sum(kcsw_rkdw) as bqrkdw ,sum(kcsw_cksl) as bqcksl,sum(kcsw_ckdw) as bqckdw,0 as bqjcsl,0 as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq between '20041220' and '20041220'
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
union all
select wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj ,0 as sqjcsl,0 as sqjcdw,0 as bqrksl,0 as bqrkdw,0 as bqcksl,0 as bqckdw ,sum(kcsw_rksl)-sum(kcsw_cksl) as bqjcsl ,sum(kcsw_rkdw)-sum(kcsw_rkdw) as bqjcdw
from kcsw ,wl where kcsw_wlid=wl_wlid and kcsw_jzrq <='20041220' group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
) a group by group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
zhangzs8896 2004-12-21
  • 打赏
  • 举报
 回复
select 写你的字段
from
(
)a
group by wl_wlid,kcsw_ph,wl_mc,wl_gg,wl_cz,wl_dj
精品课：MySQL5.7零基础入门与实战（精讲）
union all 09-数据排序 order by 10-小节 in,or,union 11-统计函数 count 12- 统计函数 sum,avg 13- 统计函数max,min 14- 排重 distinct 15- 数据分组 group by&having 16- 分页limit 17- 字符串函数 18-日期...
union和union all的区别,union 和 union all + group by 去重效果
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。...在此建了两张表来比较union和union all的区别: stu
union all 和 group by 的顺序问题
通过三个实验，来看 union all 及 group by 不同的使用顺序对CPU时间及耗时的影响 1. 先Union All， 再Group By select inctmid ctmid,cnlid,inwhsid whsid,sum(qty) qty,goodsid  from ( select inctmid,...
hive union底层原理，union与union all再group by的性能对比
union：将多个结果集合并为一个结果集，结果集去重。 union all：将多个结果集...两种写法：一种是采用union的方法，另一种是采用union all再进行group by的方法（将不去重的结果集利用group by去重）。 接下来直...
两表union 如何保证group by 字段唯一
写法一：group by之后union all ，之后再次group by保证分组字段的唯一。Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；写法二：开始不分组，将查到的分组字段union all 之后group by。...
MS-SQL Server

34,575

社区成员

254,585

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章