求教统计函数rollup,cube,该如何处理

求教统计函数rollup,cube
原数据
A B C D
DINNER1Day175.00
LUNCH1Day171.50
DINNER2Day176.75
LUNCH2Day1196.92
LUNCH2Day20.00
LUNCH2Day50.00
DINNER3Day142.52
LUNCH3Day138.00
LUNCH3Day30.00
LUNCH3Day40.00
LUNCH3Day60.00
LUNCH3Day70.00
结果
DAILY1Day1146.50
DAILY2Day1273.67
DAILY3Day180.52
DAILY2Day20.00
DAILY3Day30.00
DAILY3Day40.00
DAILY2Day50.00
DAILY3Day60.00
DAILY3Day70.00
就是根据B和C sum(D) 再把这列名字换为'DAILY'
就叫

[解决办法]

SQL code

if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (A nvarchar(12),B int,C nvarchar(8),D numeric(5,2))insert into [TB]select 'DINNER',1,'Day1',75.00 union allselect 'LUNCH',1,'Day1',71.50 union allselect 'DINNER',2,'Day1',76.75 union allselect 'LUNCH',2,'Day1',196.92 union allselect 'LUNCH',2,'Day2',0.00 union allselect 'LUNCH',2,'Day5',0.00 union allselect 'DINNER',3,'Day1',42.52 union allselect 'LUNCH',3,'Day1',38.00 union allselect 'LUNCH',3,'Day3',0.00 union allselect 'LUNCH',3,'Day4',0.00 union allselect 'LUNCH',3,'Day6',0.00 union allselect 'LUNCH',3,'Day7',0.00select * from [TB]SELECT 'DAILY' AS A,B,C,SUM(D) AS DFROM dbo.TBGROUP BY B,CORDER BY B,C/*A    B    C    DDAILY    1    Day1    146.50DAILY    2    Day1    273.67DAILY    2    Day2    0.00DAILY    2    Day5    0.00DAILY    3    Day1    80.52DAILY    3    Day3    0.00DAILY    3    Day4    0.00DAILY    3    Day6    0.00DAILY    3    Day7    0.00*/