t1
id name
1 a
2 b
3 c
t2
id t1id money isFlag
1 1 500 0
2 1 300 1
3 2 100 1我要的结果是:
id name money isFlag
1 a 300 1
2 b 100 1
3 c null null请问用一个外连接能否搞定?还是要先建立一个t2的临时表?
id name
1 a
2 b
3 c
t2
id t1id money isFlag
1 1 500 0
2 1 300 1
3 2 100 1我要的结果是:
id name money isFlag
1 a 300 1
2 b 100 1
3 c null null请问用一个外连接能否搞定?还是要先建立一个t2的临时表?
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @t1
DECLARE @t1 TABLE (id INT,name VARCHAR(1))
INSERT INTO @t1
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'c'
--> 生成测试数据: @t2
DECLARE @t2 TABLE (id INT,t1id INT,money INT,isFlag INT)
INSERT INTO @t2
SELECT 1,1,500,0 UNION ALL
SELECT 2,1,300,1 UNION ALL
SELECT 3,2,100,1--SQL查询如下:SELECT
A.id,
A.name,
B.money,
B.isFlag
FROM @t1 AS A
LEFT JOIN @t2 AS B
ON A.id=B.t1id
AND B.isFlag=1/*
id name money isFlag
----------- ---- ----------- -----------
1 a 300 1
2 b 100 1
3 c NULL NULL(3 行受影响)
*/
SELECT A.id, A.name,B.money,B.isFlag
FROM @t1 AS A
LEFT JOIN @t2 AS B
ON A.id=B.t1id
FROM t1 A
LEFT JOIN t2 B
ON A.id=B.t1id AND B.isFlag=1
id name money
1 a 100
2 a 200
t2
id name money1
1 a 500
2 a 300
3 b 100 我要的结果是:
id name money money1
1 a 300 800
3 b null 100 sql这么写的:SELECT
A.name,
sum(A.money),sum(B.money1)
FROM @t1 AS A
LEFT JOIN @t2 AS B
ON A.name=B.nameGROUP BY A.name,B.name
但是结果却是:
id name money money1
1 a 600 1600
3 b null 200 都翻倍了...
id name money money1
1 a 600 1600
3 b null 100
group by a.name
SELECT A.id,A.name,B.money,B.isFlag
FROM t1 AS A
LEFT JOIN t2 AS B ON A.id=B.t1id
AND B.isFlag=1