try:
/*
id cls_code booked
1 a 5
1 b 10
1 c 8
2 a 7
这是a表
id flt_num
1 9901
1 9901
1 9901
2 9903
flt_num a b c
9901 5 10 8
9902 7
*/
CREATE TABLE #T1([id] int,cls_code char(2),booked int)
CREATE TABLE #T2([id] int,flt_num int)
INSERT INTO #T1
SELECT 1,'a',5 UNION ALL
SELECT 1,'b',10 UNION ALL
SELECT 1,'c',8 UNION ALL
SELECT 2,'a',7
INSERT INTO #T2
SELECT 1,9901 UNION ALL
SELECT 1,9901 UNION ALL
SELECT 1,9901 UNION ALL
SELECT 2,9902
SELECT A.flt_num,B.a,B.b,B.c FROM
(SELECT [id],flt_num FROM #T2 GROUP BY [id],flt_num) AS A INNER JOIN
(SELECT [id],SUM(CASE cls_code WHEN 'a' THENbooked ELSE 0 END) AS 'a'
,SUM(CASE cls_code WHEN 'b' THENbooked ELSE 0 END) AS 'b'
,SUM(CASE cls_code WHEN 'c' THENbooked ELSE 0 END) AS 'c'
FROM #T1 GROUP BY [id]) AS B
ON B.[id]=A.[id]
DROP TABLE #T1,#T2
复制代码
|
