求sql高手哈
本帖最后由 jiuni31 于 2013-11-13 16:22:45 编辑 S_Code YEAR Month Level num
B1024 2013 11 A级 14
B1024 2013 11 B级 3
B1060 2013 11 A级 1
B1402 2013 11 B级 3
B1538 2013 11 B级 2
B1538 2013 11 C级 2
B309 2013 11 B级 1
B465 2013 11 其他 22
B716 2013 11 其他 1
这是我查询出来的一个结果集
我想在给每个S_code 加一个总计行
B1024 2013 11 A级 14
B1024 2013 11 B级 3
B1024 2013 11 总计 17
B1060 2013 11 A级 1
B1060 2013 11 总计 1
B1402 2013 11 B级 3
B1402 2013 11 总计 3
B1538 2013 11 B级 2
B1538 2013 11 C级 2
B1538 2013 11 总计 4
如何实现。。。。。 查询结果后加一行统计
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-13 16:24:15
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([S_Code] varchar(5),[YEAR] int,[Month] int,[Level] varchar(4),[num] int)
insert [huang]
select 'B1024',2013,11,'A级',14 union all
select 'B1024',2013,11,'B级',3 union all
select 'B1060',2013,11,'A级',1 union all
select 'B1402',2013,11,'B级',3 union all
select 'B1538',2013,11,'B级',2 union all
select 'B1538',2013,11,'C级',2 union all
select 'B309',2013,11,'B级',1 union all
select 'B465',2013,11,'其他',22 union all
select 'B716',2013,11,'其他',1
--------------开始查询--------------------------
SELECT [S_Code],[YEAR],[Month],[level],num
FROM (
SELECT *,ROW_NUMBER()OVER(ORDER BY [Level])id
FROM huang
UNION ALL
select [S_Code],[YEAR],[Month],'总计' [level],SUM(num),10000 AS id
from [huang]
GROUP BY [S_Code],[YEAR],[Month] )b
ORDER BY [S_Code],[YEAR],[Month],id
----------------结果----------------------------
/*
S_Code YEAR Month level num
------ ----------- ----------- ----- -----------
B1024 2013 11 A级 14
B1024 2013 11 B级 3
B1024 2013 11 总计 17
B1060 2013 11 A级 1
B1060 2013 11 总计 1
B1402 2013 11 B级 3
B1402 2013 11 总计 3
B1538 2013 11 B级 2
B1538 2013 11 C级 2
B1538 2013 11 总计 4
B309 2013 11 B级 1
B309 2013 11 总计 1
B465 2013 11 其他 22
B465 2013 11 总计 22
B716 2013 11 其他 1
B716 2013 11 总计 1
*/
create table jn
(S_Code varchar(10),[YEAR] int,[Month] int,Level varchar(10),num int)
insert into jn
select 'B1024',2013,11,'A级',14 union all
select 'B1024',2013,11,'B级',3 union all
select 'B1060',2013,11,'A级',1 union all
select 'B1402',2013,11,'B级',3 union all
select 'B1538',2013,11,'B级',2 union all
select 'B1538',2013,11,'C级',2 union all
select 'B309',2013,11,'B级',1 union all
select 'B465',2013,11,'其他',22 union all
select 'B716',2013,11,'其他',1
select S_Code,[YEAR],[Month],
isnull(Level,'总计') 'Level',
sum(num) 'num'
from jn
group by S_Code,[YEAR],[Month],Level with rollup
having grouping([YEAR])=0 and grouping([Month])=0
/*
S_Code YEAR Month Level num
---------- ----------- ----------- ---------- -----------
B1024 2013 11 A级 14
B1024 2013 11 B级 3
B1024 2013 11 总计 17
B1060 2013 11 A级 1
B1060 2013 11 总计 1
B1402 2013 11 B级 3
B1402 2013 11 总计 3
B1538 2013 11 B级 2
B1538 2013 11 C级 2
B1538 2013 11 总计 4
B309 2013 11 B级 1
B309 2013 11 总计 1
B465 2013 11 其他 22
B465 2013 11 总计 22
B716 2013 11 其他 1
B716 2013 11 总计 1
(16 row(s) affected)
*/
