一直以为自己SQL挺好的,没有想到今天在重构存储过程遇到了一个子查询的问题,修改为自连接之后发现居然结果不对,于是有了下面的测试。假设表中有如下数数据,请问Query1,Query2,Query3的查询结果?
ID | OrderID | TransID | Type |
1 | 1 | 1 | E |
2 | 1 | 1 | A |
3 | 1 | 1 | B |
4 | 1 | 2 | A |
5 | 1 | 2 | B |
6 | 2 | 1 | A |
7 | 2 | 1 | B |
8 | 2 | 2 | A |
--Query 1 SELECT * FROM OrderDetail a left join OrderDetail b ON a.OrderID=b.OrderID and a.TransID=b.TransID and b.Goods='E' WHERE b.Goods is null --Query 2 SELECT * FROM OrderDetail a WHERE a.TransID <> ( SELECT top 1 b.TransID FROM OrderDetail b WHERE b.OrderID=a.OrderID and b.Goods='E' ) --Query 3 SELECT * FROM OrderDetail a WHERE NOT EXISTS ( SELECT b.TransID,b.orderid FROM OrderDetail b WHERE b.OrderID=a.OrderID and b.Goods='E' )
在查看运行结果前请默默的写下您的答案:
Query1
ID OrderID TransID Goods
4 1 2 A
5 1 2 B
6 2 1 A
7 2 1 B
8 2 2 A
Query2
ID OrderID TransID Goods
4 1 2 A
5 1 2 B
Query3
ID OrderID TransID Goods
6 2 1 A
7 2 1 B
8 2 2 A