回答网友提问时发现左连接的问题,不知为何以下两种写法结果不一样
USE tempdb
CREATE TABLE Bus
( ID INT IDENTITY PRIMARY KEY,
[Name] NVARCHAR(50),
[Time] DATETIME,
State NVARCHAR(10)
)
INSERT INTO BUS ( [Name], [Time], [State])
SELECT N'汽车A', '2001-1-1 1:1:1' , N'出发'
UNION
SELECT N'汽车A' , '2001-1-1 2:2:2', N'返回'
UNION
SELECT N'汽车B' , '2001-1-1 3:3:3', N'出发'
UNION
SELECT N'汽车C' , '2001-1-1 5:5:5', N'出发'
UNION
SELECT N'汽车B' , '2001-1-1 4:4:4', N'返回'
UNION
SELECT N'汽车C' , '2001-1-1 6:6:6', N'返回'
UNION
SELECT N'汽车D' , '2001-1-1 7:7:7', N'出发'
--STATE1
SELECT A.NAME,A.TIME AS '出发时间',B.Time as '返回时间'
FROM Bus A
LEFT JOIN (SELECT Name, Time
FROM Bus c
WHERE c.State = N'返回'
)B ON A.Name = B.Name
WHERE A.State=N'出发'
--STATE2
SELECT A.NAME, A.TIME AS '出发时间', B.Time as '返回时间'
FROM Bus A
LEFT JOIN Bus B ON A.Name = B.Name
WHERE A.State=N'出发'
AND B.State=N'返回'
DROP TABLE Bus