我正在尝试比较两个表A
和B
,以查找A
存在但B
中不存在相同记录的实例。
表A:
|PROJECT|ID|USER|DATE|
+-------+--+----+----+
| 2 |1 |ASD |0624|
| 3 |2 |FGH |0624|
表B:
|PROJECT|ID|USER|DATE|
+-------+--+----+----+
| 2 |1 |ASD |0624|
我希望看到这样的输出:
|PROJECT|ID|USER|DATE|MATCHING_ID|
+-------+--+----+----+-----------+
| 2 |1 |ASD |0624| 1 |
| 3 |2 |FGH |0624| NONE |
我尝试过这样的方法,但我只是遇到了语法或不明确的列名错误。我不完全确定我做错了什么。我举了几个例子,但都落到了同样的境地。
SELECT [PROJECT], [ID], [USER], [DATE]
FROM [TABLE_A]
LEFT JOIN [TABLE_B] ON [ID] = [ID]
WHERE [DATE] >= DATEADD(mm, -2, GETDATE())
发布于 2019-06-26 04:39:42
修复您的JOIN
条件并使用COALESCE()
SELECT A.*, COALESCE(B.ID, 'NONE') as MATCHING_ID
FROM [TABLE_A] A LEFT JOIN
[TABLE_B] B
ON A.[ID] = B.[ID] AND
A.[DATE] = B.[DATE] AND
A.PROJECT = B.PROJECT AND
A.USER = B.USER;
发布于 2019-06-26 04:46:15
您可以使用CASE语句和EXISTS:
SELECT a.*,
CASE WHEN EXISTS (
SELECT 1 FROM [TABLE_B] b
WHERE a.PROJECT = b.PROJECT AND a.ID = b.ID AND a.USER = b.USER AND a.DATE = b.DATE
) THEN a.ID ELSE 'NONE' END MATCHING_ID
FROM [TABLE_A] a
https://stackoverflow.com/questions/56761677
复制相似问题