解决方案 »
- 问个生成列名的问题
- 如何更新自动增长列的数值为其它数值,前提是不取消其自动增长的特点.
- 请问在查询分析器中如何调用事件探测器?
- 关于SQL JOIN ON 查寻的问题.
- 连接字符串大全(适合新手与开发人员)
- BCP导入导出,列顺序不对,导入的数据错位问题???请大虾帮忙
- 为什么我的win2003不能够安装sql server 2000?
- 求助 多表连接问题
- 下面2句起什么作用?
- sql server 的datetime类型 如何 like 'date%' ,比如数据库 datetime 为 '2001-01-01 09:09:09' 如何找出 日期为'2001-01-01'的纪录
- 简单的插入触发器,怎么修改,在线等
- 求讲解 OPENDATASOURCE & OPENROWSET 用法
WITH a1 AS
(
SELECT S_Code,YEAR,Month,Level,num,ROW_NUMBER() OVER(PARTITION BY S_Code,YEAR,MONTH ORDER BY @@servername) re
FROM 表
)
,a2 AS
(
SELECT S_Code,YEAR,MONTH,SUM(num) num,MAX(re) re
FROM a1
GROUP BY S_Code,YEAR,MONTH
)
SELECT S_Code,YEAR,Month,Level,num, re FROM a1
UNION ALL
SELECT S_Code,YEAR,Month,'总计',num, re+1 FROM a1
ORDER BY S_Code,YEAR,Month,re
-- 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
*/
SELECT [S_Code],[YEAR],[Month],ISNULL([Level],'总计'),[num] FROM
(
SELECT [S_Code],[YEAR],[Month],[Level],SUM([num]) AS [num] FROM #tb
GROUP BY [S_Code],[YEAR],[Month],[Level]
WITH ROLLUP
) t
WHERE [YEAR] IS NOT NULL
AND [Month] IS NOT NULL
cool 爱死你了!!!!稍微解释下呗。
go create table tb(
[S_Code] varchar(5),[YEAR] int,
[Month] int,[Level] varchar(4),[num] int
)insert tb
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 [S_Code],[YEAR],[Month],isnull([Level],'总计') as level,SUM(num) as num,
case when GROUPING([S_Code]) = 0 and
GROUPING([YEAR]) = 0 and
GROUPING([Month]) = 0
then 1
else 0
end flag
from tb
group by rollup([S_Code],[YEAR],[Month],[Level])
)t
where flag= 1
/*
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)
*/
呵呵,是不是用rollup是的
貌似不错,利用SQL自带的WITH ROLLUP进行分组!