两个表连接查询的问题

[gugelu]

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的临时表？

[快乐_石头]

select a.*,b.money,b.isflag from t1 a left join t2 b on a.id=b.t1id

[liangCK]

t2表里的同一t1id只有一个isFlag=1?

[liangCK]

---------------------------------
--  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 行受影响)
*/

[Grave_rain]

一个左外链接搞定，t1左，t2右，
SELECT A.id, A.name,B.money,B.isFlag
FROM @t1 AS A
LEFT JOIN @t2 AS B
ON A.id=B.t1id

[-狙击手-]

SELECT *
FROM t1 A
LEFT JOIN t2  B
ON A.id=B.t1id AND B.isFlag=1

[dejiang_ren]

学习

[gugelu]

t1
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.name

GROUP BY A.name,B.name


但是结果却是：
id name money money1
1 a 600 1600
3 b null 200

都翻倍了...

[gugelu]

修正，错误的结果是：

id name money money1
1 a 600 1600
3 b null 100

[snail613]

[Quote=引用 7 楼 gugelu 的回复:]
t1
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.name

GROUP BY A.name,B.name


但是结果却是：…
[/Quote]


select a.name,sum(a.money) money,(select sum(b.money1) from t2 b where b.name='a') money1 from t1 a where a.name='a'
group by a.name

[jinlongjavaornet]

[Quote=引用 3 楼 liangCK 的回复:]
SQL code---------------------------------
-- 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…
[/Quote]

强

[Zoezs]

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

[gugelu]

请高手回答7楼问题，谢谢