日期:2014-05-17 浏览次数:20510 次
if object_id('[TB]') is not null drop table [TB] go create table [TB] (units nvarchar(6),flag nvarchar(6),kind nvarchar(2),Money int) insert into [TB] select '名称1','标志1','A',10 union all select '名称1','标志2','B',23 union all select '名称2','标志3','A',4 union all select '名称2','标志2','A',6 union all select '名称3','标志3','A',77 union all select '名称3','标志3','B',6 select * from [TB] SELECT units AS '名称', SUM(CASE WHEN flag='标志1' AND kind = 'A' THEN money ELSE 0 END) AS '标志1_A', SUM(CASE WHEN flag='标志1' AND kind = 'B' THEN money ELSE 0 END) AS '标志1_B', SUM(CASE WHEN flag='标志2' AND kind = 'A' THEN money ELSE 0 END) AS '标志2_A', SUM(CASE WHEN flag='标志2' AND kind = 'B' THEN money ELSE 0 END) AS '标志2_B', SUM(CASE WHEN flag='标志3' AND kind = 'A' THEN money ELSE 0 END) AS '标志3_A', SUM(CASE WHEN flag='标志3' AND kind = 'B' THEN money ELSE 0 END) AS '标志3_B' FROM dbo.TB GROUP BY units /* 名称 标志1_A 标志1_B 标志2_A 标志2_B 标志3_A 标志3_B ------ ----------- ----------- ----------- ----------- ----------- ----------- 名称1 10 0 0 23 0 0 名称2 0 0 6 0 4 0 名称3 0 0 0 0 77 6 (3 行受影响) */
------解决方案--------------------
select units 名称, sum(case when flag='标志1' and kind='A' then [Money] else 0 end) A1, sum(case when flag='标志1' and kind='B' then [Money] else 0 end) B1, sum(case when flag='标志2' and kind='A' then [Money] else 0 end) A2, sum(case when flag='标志2' and kind='B' then [Money] else 0 end) B2, sum(case when flag='标志3' and kind='A' then [Money] else 0 end) A3, sum(case when flag='标志3' and kind='B' then [Money] else 0 end) B3 from 表 group by units;
------解决方案--------------------
--沙发---
CREATE TABLE fkind(units VARCHAR2(20),flag VARCHAR2(20),kind VARCHAR2(10),money NUMBER);
INSERT INTO fkind VALUES('名称1','标志1','A',10);
INSERT INTO fkind VALUES('名称1','标志2','B',23);
INSERT INTO fkind VALUES('名称2','标志3','A',4);
INSERT INTO fkind VALUES('名称2','标志2','A',6);
INSERT INTO fkind VALUES('名称1','标志1','A',77);
INSERT INTO fkind VALUES('名称3','标志3','B',6);
COMMIT;
SELECT f.Units 名称,
SUM(CASE
WHEN Flag = '标志1' AND Kind = 'A' THEN
f.Money
ELSE
0
END) Asum,
SUM(CASE
WHEN Flag = '标志1' AND Kind = 'B' THEN
f.Money
ELSE
0
END) Bsum,
SUM(CASE
WHEN Flag = '标志2' AND Kind = 'A' THEN
f.Money
ELSE
0
END) Asum,
SUM(CASE
WHEN Flag = '标志2' AND Kind = 'B' THEN
f.Money
ELSE
0
END) Bsum,
SUM(CASE
WHEN Flag = '标志3' AND Kind = 'A' THEN
f.Money
ELSE
0
END) Asum,
SUM(CASE
WHEN Flag = '标志3' AND Kind = 'B' THEN
f.Money
ELSE
0
END) Bsum