110,533
社区成员
发帖
与我相关
我的任务
分享
create table MeterialInfo
(
id int identity(1,1),
MeterialName varchar(50),
InStoreDate datetime,
Weight decimal(18,3),
Remark varchar(200)
)
select 100*RAND()
insert into MeterialInfo values ('AAAAA',GETDATE(),100*RAND(),NEWID())
waitfor delay '00:00:01:01'
insert into MeterialInfo values ('BBBBB',GETDATE(),100*RAND(),NEWID())
waitfor delay '00:00:01:01'
insert into MeterialInfo values ('AAAAA',GETDATE(),100*RAND(),NEWID())
waitfor delay '00:00:01:01'
insert into MeterialInfo values ('BBBBB',GETDATE(),100*RAND(),NEWID())
select
CASE WHEN GROUPING(MeterialName) = 1
THEN 'ALL' ELSE MeterialName END
AS MeterialName,
SUM(Weight) from MeterialInfo
group by MeterialName WITH CUBE
--MeterialName (无列名)
--AAAAA 217.411
--BBBBB 274.565
--ALL 491.976
select
CASE WHEN GROUPING(MeterialName) = 1
THEN 'ALL' ELSE MeterialName END
AS MeterialName,InStoreDate,
SUM(Weight) from MeterialInfo
group by MeterialName ,InStoreDate WITH ROLLUP
MeterialName InStoreDate (无列名)
AAAAA 2012-12-12 11:16:43.957 76.909
AAAAA 2012-12-12 11:16:45.957 60.072
AAAAA 2012-12-12 11:16:47.707 53.246
AAAAA 2012-12-12 11:16:49.707 27.184
AAAAA 2012-12-12 11:32:53.433 89.429
AAAAA 2012-12-12 11:32:53.620 21.208
AAAAA 2012-12-12 11:32:53.637 90.963
AAAAA NULL 419.011
BBBBB 2012-12-12 11:16:44.957 68.751
BBBBB 2012-12-12 11:16:46.957 33.855
BBBBB 2012-12-12 11:16:48.707 77.566
BBBBB 2012-12-12 11:16:50.720 94.393
BBBBB 2012-12-12 11:32:53.433 96.330
BBBBB 2012-12-12 11:32:53.620 72.308
BBBBB 2012-12-12 11:32:53.637 43.261
BBBBB NULL 486.464
ALL NULL 905.475