第一个表是数据库的,第二个表是要呈现出来的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 */
帮助里查下 group by 的用法就知道了
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 t1SELECT id, 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] FROM t1 GROUP BY idid 类型1 价格1 类型2 价格2 1 A 2.3000 B 1.2000 2 A 0.0000 B 3.8000 3 A 5.9000 B 0.0000 4 A 1.3000 B 4.6000
;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 t1SELECT id,
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]
FROM t1
GROUP BY idid 类型1 价格1 类型2 价格2
1 A 2.3000 B 1.2000
2 A 0.0000 B 3.8000
3 A 5.9000 B 0.0000
4 A 1.3000 B 4.6000