原数据
A B C D
DINNER 1 Day1 75.00
LUNCH 1 Day1 71.50
DINNER 2 Day1 76.75
LUNCH 2 Day1 196.92
LUNCH 2 Day2 0.00
LUNCH 2 Day5 0.00
DINNER 3 Day1 42.52
LUNCH 3 Day1 38.00
LUNCH 3 Day3 0.00
LUNCH 3 Day4 0.00
LUNCH 3 Day6 0.00
LUNCH 3 Day7 0.00
结果
DAILY 1 Day1 146.50
DAILY 2 Day1 273.67
DAILY 3 Day1 80.52
DAILY 2 Day2 0.00
DAILY 3 Day3 0.00
DAILY 3 Day4 0.00
DAILY 2 Day5 0.00
DAILY 3 Day6 0.00
DAILY 3 Day7 0.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*/