请问sql分类汇总的写法-MSSQL教程-爱易网页

请问sql分类汇总的写法

日期：2014-05-18　浏览次数：20858 次

请教sql分类汇总的写法
例表：
/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp]
--GO

CREATE TABLE [dbo].[temp] (
[id] [varchar] (10)NULL ,
[store] [varchar] (50) NULL ,
[name] [varchar] (50) NULL ,
[description] [varchar](50) NULL ,
[unit] [varchar] (50) NULL ,
[quantity] [int] NULL ,
[value] [money] NULL ,
[date] [datetime] NULL
) ON [PRIMARY]
--GO

insert temp values('2501','west','pen', 'blue', 'ea',1,5.0000 ,2007-01-01 )
insert temp values('2501','east','pen', 'red', 'ea',2,10.0000,2007-02-05 )
insert temp values('2501','west','pen', 'yellow','ea',3,15.0000,2007-03-05 )
insert temp values('1115','north','cup', '', 'ea',5,50.0000,2007-02-03 )
insert temp values('1115','north','cup', '', 'ea',2,20.0000,2007-03-05 )
*/

求该表按id分类汇总，同ID的东西只小计和总计quantity,value，请教这个语句得怎么写？
以下是我写的，查询出来多了很多重复的行，多的原因是同时按store,name,description,unit,date进行了group 分组，
select case when (grouping (id)=1)then '总计'else isnull (id,'unknown') end as id,
case when (grouping (store)=1)then '小计'else isnull (store,'unknown') end as store,
name,description,unit,sum(quantity) as quantity,sum(value)as value,date
from temp
group by id,store,name,description,unit,date
with rollup

以上代码可以直接在查询分析器中使用

------解决方案--------------------

SQL code

declare @tb table (id varchar(10),name varchar(1),dt datetime,num int)
insert into @tb select '01','a','2008-01-01',1
insert into @tb select '01','a','2008-01-01',2
insert into @tb select '01','a','2008-01-02',3
insert into @tb select '01','a','2008-01-02',4
insert into @tb select '02','b','2008-01-01',5
insert into @tb select '02','b','2008-01-01',6
insert into @tb select '02','b','2008-01-02',7
insert into @tb select '02','b','2008-01-02',8

select id,name,dt,sum(num) as num from @tb
group by id,name ,dt
with rollup
having grouping(id)+grouping(name)+grouping(dt)!=1

------解决方案--------------------

SQL codedeclare @tb table (id varchar(10),name varchar(1),dt datetime,num int)
insert into @tb select '01','a','2008-01-01',1
insert into @tb select '01','a','2008-01-01',2
insert into @tb select '01','a','2008-01-02',3
insert into @tb select '01','a','2008-01-02',4
insert into @tb select '02','b','2008-01-01',5
insert into @tb select '02','b','2008-01-01',6
insert into @tb select '02','b','2008-01-02',7
insert into @tb select '02','b','2008-01-02',8

select 
case when grouping(id)=1 then '合计' else id end as id,
case when grouping(id)+grouping(name)=1 then '小计' else name end as name,
dt,
sum(num) as num from @tb
group by id,name ,dt
with rollup
having grouping(id)+grouping(name)+grouping(dt)!=1



id name dt num 
01 a 2008-01-01   00:00:00.000 3 
01 a 2008-01-02   00:00:00.000 7 
01 小计 NULL 10 
02 b 2008-01-01   00:00:00.000 11 
02 b 2008-01-02   00:00:00.000 15 
02 小计 NULL 26 
合计 NULL NULL 36

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

请问sql分类汇总的写法

相关资料更多>

推荐阅读更多>