SQL Server:连接两个表并返回空记录 [英] SQL Server : Join Two Tables and Return With Null Records

问题描述

我不知道如何连接这两个表并获得我想要的 NULL 值的结果.我尝试了 LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN...，但无法让它工作.请参阅下面的内容.

I couldn't figure out how to join these two tables and get the result with NULL value that I want. I played around with LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN..., but couldn't get it to work. Please see this below.

表 1:

NameID  Name
1       N1
2       N2
3       N3
4       N4
5       N5

表 2:

NameID  AnotherID   Value
1       AID-111     1000
2       AID-222     2000
2       AID-222     3000
3       AID-333     4000
4       AID-444     5000


Select ...
JOIN Table1 and Table2
WHERE AnotherID = 'AID-222' 

这是我想要的结果:

NameID  Name    AnotherID   VALUE
1       N1      NULL        NULL
2       N2      AID-222     2000
3       N3      AID-222     3000    
4       N4      NULL        NULL    
5       N5      NULL        NULL

请帮忙.谢谢！

推荐答案

你没有解释为什么要返回 2000 的值而不是 3000 但你可以使用带有子查询的 LEFT JOIN 来获取结果:

You did not explain why you want to return the value of 2000 instead of 3000 but you can use a LEFT JOIN with a subquery to get the result:

select t1.nameid,
  t1.name,
  t2.anotherid,
  t2.value
from table1 t1
left join
(
  select nameid, anotherid, min(value) value
  from table2
  where anotherid = 'AID-222'
  group by nameid, anotherid
) t2
  on t1.nameid = t2.nameid;

请参阅SQL Fiddle with Demo.这给出了结果:

See SQL Fiddle with Demo. This gives the result:

| NAMEID | NAME | ANOTHERID |  VALUE |
--------------------------------------
|      1 |   N1 |    (null) | (null) |
|      2 |   N2 |   AID-222 |   2000 |
|      3 |   N3 |    (null) | (null) |
|      4 |   N4 |    (null) | (null) |
|      5 |   N5 |    (null) | (null) |

这篇关于SQL Server:连接两个表并返回空记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！