--> 生成测试数据: @TA DECLARE @TA TABLE (id INT,value INT) INSERT INTO @TA SELECT 1,2 UNION ALL SELECT 2,2 UNION ALL SELECT 3,4 UNION ALL SELECT 4,5
--> 生成测试数据: @TB DECLARE @TB TABLE (id INT,aid INT,value INT) INSERT INTO @TB SELECT 1,1,3 UNION ALL SELECT 2,2,4--SQL查询如下:SELECT A.id, CASE WHEN B.aid IS NULL AND B.value IS NULL THEN NULL ELSE A.value END AS value FROM @TA AS A LEFT JOIN @TB AS B ON A.id=B.aid AND B.value = 3/* id value ----------- ----------- 1 2 2 NULL 3 NULL 4 NULL(4 row(s) affected) */
codeSELECT A.id, CASE WHEN B.aid IS NULL AND B.value IS NULL THEN NULL ELSE A.value END AS value FROM A LEFT JOIN B ON (A.id=B.aid AND B.value )= 3
select a.id,if(b.aid is null,null,a.value) from a left join (select aid from b where value=3) t on a.id=b.aid
A.*
FROM A
LEFT JOIN B
ON A.id=B.aid AND B.value = 3
A.id,
CASE WHEN B.aid IS NULL AND B.value IS NULL THEN NULL ELSE A.value END AS value
FROM A
LEFT JOIN B
ON A.id=B.aid AND B.value = 3
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @TA
DECLARE @TA TABLE (id INT,value INT)
INSERT INTO @TA
SELECT 1,2 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,4 UNION ALL
SELECT 4,5
--> 生成测试数据: @TB
DECLARE @TB TABLE (id INT,aid INT,value INT)
INSERT INTO @TB
SELECT 1,1,3 UNION ALL
SELECT 2,2,4--SQL查询如下:SELECT
A.id,
CASE WHEN B.aid IS NULL AND B.value IS NULL THEN NULL ELSE A.value END AS value
FROM @TA AS A
LEFT JOIN @TB AS B
ON A.id=B.aid AND B.value = 3/*
id value
----------- -----------
1 2
2 NULL
3 NULL
4 NULL(4 row(s) affected)
*/
codeSELECT
A.id,
CASE WHEN B.aid IS NULL AND B.value IS NULL THEN NULL ELSE A.value END AS value
FROM A
LEFT JOIN B
ON (A.id=B.aid AND B.value )= 3
from a left join (select aid from b where value=3) t on a.id=b.aid