不需要排序就这样:
select f2,f3,f4,f5 from tablename
union all
select f1 as f2,sum(f3),'','' from tablename group by f1
需要排序就这样:
select f2,f3,f4,f5 from (
select f2,f3,f4,f5,f1 as paixu from tablename
union all
select f1 as f2,sum(f3),'','',f1 as paixu from tablename group by f1) a
order by paixu ,f4,f5
select f2,f3,f4,f5 from tablename
union all
select f1 as f2,sum(f3),'','' from tablename group by f1
需要排序就这样:
select f2,f3,f4,f5 from (
select f2,f3,f4,f5,f1 as paixu from tablename
union all
select f1 as f2,sum(f3),'','',f1 as paixu from tablename group by f1) a
order by paixu ,f4,f5
(
F1 varchar(10),
F2 varchar(10),
F3 int,
F4 varchar(10),
F5 varchar(10)
)
insert A
select 'A', 'CCC', 2, 'C', '34' union
select 'B', 'AAA', 5, 'F', '66' union
select 'A', 'VVV', 3, 'S', 'HH'--查询
select F2,F3,F4,F5
from
(select F2,F3,F4,F5,F1 from A
union
select F1,sum(F3),null,null,F1 from A group by F1
)t
order by F1,F4--删除测试环境
drop table A--结果
/*
F2 F3 F4 F5
---------- ----------- ---------- ----------
A 5 NULL NULL
CCC 2 C 34
VVV 3 S HH
B 5 NULL NULL
AAA 5 F 66(所影响的行数为 5 行)
*/
from
(select F2,F3,F4,F5,F1 from A
union
select F1,sum(F3),null,null,F1 from A group by F1
)t
o|rder by F1,F4
|
這個 t是什麼意思呢?
from
(select DrAdvice,AllTotal,id,DrAdviceID,FeetypeName from VIEWCheck where sendno='519'
union
select FeetypeName,sum(AllTotal),null,null,FeetypeName from VIEWCheck where sendno='519' group by FeetypeName
)t
order by FeetypeName,id运行提示:服务器: 消息 8618,级别 16,状态 2,行 1
警告: 查询处理器未能用优化程序生成查询计划,因为 GROUP BY 或 ORDER BY 子句中所有列的总长度超过了 8000 字节。
select F2,F3,F4,F5
from
(select F2,F3,F4,F5 from A
union
select F1,sum(F3),'','' from A group by F1
) order by F1,F2 DESC