22,209
社区成员
发帖
与我相关
我的任务
分享
2 51 dggh 王强 鞍山
1 51 (合计) 鞍山
4 10 uyiyui 刘流 北京
1 10 (合计) 北京
1 3 adsf 李刚 大连
3 2 oiuoi 张莉 大连
2 5 (合计) 大连
; with table1 as
(
select 1 as id, 3 as jianshu, 'adsf'as huohao, '李刚'as shouhuoren, '大连' as daozhan
union all
select 2, 51, 'dggh', '王强', '鞍山'
union all
select 3, 2, 'oiuoi', '张莉', '大连'
union all
select 4, 10, 'uyiyui', '刘流', '北京'
)
select id, jianshu, huohao, shouhuoren, daozhan from
(
select *, '1' as s from table1
union all
select count(id) as id, sum(jianshu) as jianshu, '(合计)', '', daozhan, '2' as s from table1 group by daozhan
) as a
order by daozhan, s
/* 测试数据
WITH table1(id,jianshu,huohao,shouhuoren,daozhan)AS(
SELECT 1,3,'adsf','李刚','大连' UNION ALL
SELECT 2,51,'dggh','王强','鞍山' UNION ALL
SELECT 3,2,'oiuoi','张莉','大连' UNION ALL
SELECT 4,10,'uyiyui','刘流','北京'
)*/
SELECT id,jianshu,huohao,shouhuoren,daozhan
FROM (
SELECT *,
0 flag
FROM table1
UNION ALL
SELECT COUNT(*) id,
SUM(jianshu) jianshu,
'' huohao,
'' shouhuoren,
daozhan,
1 flag
FROM table1
GROUP BY daozhan
) t
ORDER BY daozhan, flag, id
id jianshu huohao shouhuoren daozhan
----------- ----------- ------ ---------- -------
2 51 dggh 王强 鞍山
1 51 鞍山
4 10 uyiyui 刘流 北京
1 10 北京
1 3 adsf 李刚 大连
3 2 oiuoi 张莉 大连
2 5 大连