WITH table1 AS(
SELECT 1 AS a,'a1' AS b UNION ALL
SELECT 2 AS a,'a2' AS b UNION ALL
SELECT 3 AS a,'a3' AS b UNION ALL
SELECT 4 AS a,'a4' AS b UNION ALL
SELECT 5 AS a,'a5' AS b
),
table2 AS(
SELECT 1 AS aa,'a1' AS bb UNION ALL
SELECT 3 AS aa,'a4' AS bb UNION ALL
SELECT 4 AS aa,'a5' AS bb UNION ALL
SELECT 5 AS aa,'a6' AS bb UNION ALL
SELECT 6 AS aa,'a7' AS bb
)
SELECT a1.a, a1.b, b1.aa, b1.bb FROM table1 AS a1 INNER JOIN table2 AS b1 ON a1.a=b1.aa
AND EXISTS ( SELECT * FROM table2 WHERE bb=a1.b);结果是:
a b aa bb
--------------------
1 a1 1 a1
2 a2 NULL NULL
3 a3 NULL NULL
4 a4 4 a5
5 a5 5 a6但是table2中的bb='a6'在table1的b列根本就不存在,为啥还能出现最后一行呢?
SELECT 1 AS a,'a1' AS b UNION ALL
SELECT 2 AS a,'a2' AS b UNION ALL
SELECT 3 AS a,'a3' AS b UNION ALL
SELECT 4 AS a,'a4' AS b UNION ALL
SELECT 5 AS a,'a5' AS b
),
table2 AS(
SELECT 1 AS aa,'a1' AS bb UNION ALL
SELECT 3 AS aa,'a4' AS bb UNION ALL
SELECT 4 AS aa,'a5' AS bb UNION ALL
SELECT 5 AS aa,'a6' AS bb UNION ALL
SELECT 6 AS aa,'a7' AS bb
)
SELECT a1.a, a1.b, b1.aa, b1.bb FROM table1 AS a1 INNER JOIN table2 AS b1 ON a1.a=b1.aa
AND EXISTS ( SELECT * FROM table2 WHERE bb=a1.b);结果是:
a b aa bb
--------------------
1 a1 1 a1
2 a2 NULL NULL
3 a3 NULL NULL
4 a4 4 a5
5 a5 5 a6但是table2中的bb='a6'在table1的b列根本就不存在,为啥还能出现最后一行呢?
解决方案 »
- 这条语句应该怎样写?
- 求sql语句一条
- 排序规则这么重要吗?从这个库备份后无法还原到另一个库了,如何办?
- 请问在SQL server 2000的视图中是不是不允许带Case when then的语句?
- 收缩数据库日志后的疑问 数据库日志不会增大了??
- 再次求助各位,帮我看一下!谢了!
- 本觉得很简单的SQL语句,结果书都翻遍了,就是弄不出来!
- 帮我写一条sql语句吧
- windows2000主域服务器安装sqlserver2000出现问题,十万火急,请各位大虾帮帮忙
- 求助一个select ... in 的 写法
- sqlserver连接服务
- 使用Rs.AddNew为何提示ADODB.Recordset (0x800A0CB3),在线等!!
sorry