初始值:
DECLARE @A TABLE(AID INT,[UID] INT UNIQUE IDENTITY(0,1))
DECLARE @B TABLE(BID INT)
INSERT INTO @A([AID]) VALUES(1);
INSERT INTO @A([AID]) VALUES(1);
INSERT INTO @A([AID]) VALUES(1);
INSERT INTO @A([AID]) VALUES(2);
左外连接And过滤:
SELECT a.AID FROM @A a
LEFT JOIN @B b ON b.[BID] = [a].[AID]
AND NOT EXISTS(SELECT 1 FROM @A a1 WHERE a1.[AID]=a.[AID] AND a1.[UID]>a.[UID])
--结果集:
-- AID
--1
--1
--1
--2
左外连接后Where过滤
SELECT a.AID FROM @A a
LEFT JOIN @B b ON b.[BID] = [a].[AID]
WHERE NOT EXISTS(SELECT 1 FROM @A a1 WHERE a1.[AID]=a.[AID] AND a1.[UID]>a.[UID])
--结果集:
--AID
--1
--2
求解为何在左连接后的And过滤条件会失效?
------解决方案--------------------
因为左联里跟的条件不满足,左边表一样出数据,但where里条件不满足数据就被不出了