请问sql分类汇总的写法

2012-03-18

请教sql分类汇总的写法例表：/*if exists (select * from dbo.sysobjects where id object_id(N[dbo].[t

请教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',1insert into @tb select '01','a','2008-01-01',2insert into @tb select '01','a','2008-01-02',3insert into @tb select '01','a','2008-01-02',4insert into @tb select '02','b','2008-01-01',5insert into @tb select '02','b','2008-01-01',6insert into @tb select '02','b','2008-01-02',7insert into @tb select '02','b','2008-01-02',8select id,name,dt,sum(num) as num from @tbgroup by id,name ,dtwith rolluphaving 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',1insert into @tb select '01','a','2008-01-01',2insert into @tb select '01','a','2008-01-02',3insert into @tb select '01','a','2008-01-02',4insert into @tb select '02','b','2008-01-01',5insert into @tb select '02','b','2008-01-01',6insert into @tb select '02','b','2008-01-02',7insert into @tb select '02','b','2008-01-02',8select 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 @tbgroup by id,name ,dtwith rolluphaving grouping(id)+grouping(name)+grouping(dt)!=1id 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 Server

请问sql分类汇总的写法