SELECT a.s1,a.s2,
SUM(CASE WHEN b.type='a费用' THEN b.value ELSE 0 END) AS [a费用],
SUM(CASE WHEN b.type='b费用' THEN b.value ELSE 0 END) AS [a费用]
FROM T1 AS a
JOIN T2 AS b
ON a.s1=b.s1
GROUP BY a.s1,a.s2;
SUM(CASE WHEN b.type='a费用' THEN b.value ELSE 0 END) AS [a费用],
SUM(CASE WHEN b.type='b费用' THEN b.value ELSE 0 END) AS [a费用]
FROM T1 AS a
JOIN T2 AS b
ON a.s1=b.s1
GROUP BY a.s1,a.s2;
SUM(CASE WHEN b.type='a费用' THEN b.value ELSE 0 END) AS [a费用],
SUM(CASE WHEN b.type='b费用' THEN b.value ELSE 0 END) AS [b费用]
FROM T1 AS a
JOIN T2 AS b
ON a.s1=b.s1
GROUP BY a.s1,a.s2;
1楼复制的时候忘了改别名..:)
FROM T1 A INNER JOIN T2 B ON A.s1=B.S1 AND B.type='a费用'
INNER JOIN T2 C ON A.s1=C.S1 AND C.type='B费用'
CREATE TABLE T1(s1 VARCHAR(10),s2 INT)
INSERT T1 SELECT '0001' , 1
UNION ALL SELECT '0002' , 2CREATE TABLE T2(s1 VARCHAR(10),type VARCHAR(10), value INT)
INSERT T2 SELECT '0001','a费用',50
UNION ALL SELECT '0001','b费用',20
UNION ALL SELECT '0002','a费用',30
UNION ALL SELECT '0002','b费用',45
GOSELECT a.s1,a.s2,b.a费用,b.b费用 FROM t1 a
INNER JOIN
(SELECT a.s1,a.value a费用,b.value b费用 FROM t2 a INNER JOIN t2 b ON a.s1=b.s1 AND a.Type='a费用' AND a.Type!=b.Type)
b
ON a.s1=b.s1
/*
0001 1 50 20
0002 2 30 45
*/DROP TABLE t1,t2
GO
INSERT @T1 SELECT '0001',1
INSERT @T1 SELECT '0002',2
DECLARE @T2 TABLE(s1 VARCHAR(10),type VARCHAR(20) ,value INT )
INSERT @T2 SELECT '0001' ,'a费用',50
INSERT @T2 SELECT '0001' ,'b费用',20
INSERT @T2 SELECT '0002' ,'a费用',30
INSERT @T2 SELECT '0002', 'b费用',45
SELECT A.S1,A.S2,B.VALUE,C.VALUE
FROM @T1 A INNER JOIN @T2 B ON A.s1=B.S1 AND B.type='a费用'
INNER JOIN @T2 C ON A.s1=C.S1 AND C.type='B费用'
/*S1 S2 VALUE VALUE
---------- ----------- ----------- -----------
0001 1 50 20
0002 2 30 45*/