分组查询
第一个表是数据库的，第二个表是要呈现出来的UI，
怎么由第一个表得到第二个表？？
select 1 as 编号 ,2.3 as 价格,'A' as 类型 union ALL
select 1 ,1.2,'B' union ALL
select 2 ,3.8,'B' union ALL
select 3 ,5.9,'A' union ALL
select 4 ,1.3,'A' union ALL
select 4 ,4.6,'B'
;with maco as
(
select 1 as 编号 ,2.3 as 价格,'A' as 类型 union ALL
select 1 ,1.2,'B' union ALL
select 2 ,3.8,'B' union ALL
select 3 ,5.9,'A' union ALL
select 4 ,1.3,'A' union ALL
select 4 ,4.6,'B'
)
select
编号,'A' as 类型,
sum(case WHEN 类型='A' Then 价格 else 0 end) as 价格,
'B' as 类型,
sum(case WHEN 类型='B' Then 价格 else 0 end) as 价格
from maco group by 编号
/*
编号 类型 价格 类型 价格
----------- ---- --------------------------------------- ---- ---------------------------------------
1 A 2.3 B 1.2
2 A 0.0 B 3.8
3 A 5.9 B 0.0
4 A 1.3 B 4.6
*/
CREATE TABLE t1
(
id INT,
price MONEY,
style VARCHAR(1)
)
INSERT INTO t1
select 1 ,2.3,'A' union ALL
select 1 ,1.2,'B' union ALL
select 2 ,3.8,'B' union ALL
select 3 ,5.9,'A' union ALL
select 4 ,1.3,'A' union ALL
select 4 ,4.6,'B'
SELECT * FROM t1
SELECTid,
MAX(CASE WHEN style='A' THEN 'A' ELSE 'A' END) AS [类型1],
MAX(CASE WHEN style='A' THEN price ELSE 0.0 END) AS [价格1],
MAX(CASE WHEN style='B' THEN 'B' ELSE 'B' END) AS [类型2],
MAX(CASE WHEN style='B' THEN price ELSE 0.0 END) AS [价格2]
FROMt1
GROUP BY id
id类型1价格1类型2价格2
1A2.3000B1.2000
2A0.0000B3.8000
3A5.9000B0.0000
4A1.3000B4.6000
