您好，我这里有一个车辆检查数据库，并且我在sql中有一个代码，可以为我提供第二个及以后的EventTime或大于Min（EventTime）并带有相应的TicketStatus。表为[事件]和[汽车] ... [事件]表具有字段CarID（FK）；EventTime; TicketStatus ...在[Car]表上，具有字段CarID（PK）；板号 包裹
注意：[Car] .CarID数据类型是唯一标识符Ex。{16F0151E-1938-4487-BB75-FF446B4DB7FF}
执行代码将给我CarID，EventTime，TicketStatus。我不想要CarID作为结果，我想要来自表[Car]，TicketStatus，EventTime和条件[Car] .Package = 4的PlateNo
SELECT CarID, TicketStatus, [EventTime]
FROM
(SELECT ROW_NUMBER() OVER
(PARTITION BY CarID ORDER BY [EventTime] ASC) AS [index]
, CarID, TicketStatus, [EventTime]
FROM vehicle.dbo.Event Event) A
WHERE [index] > 1
如果我正确理解您想要的内容，则只需要JOIN使用这样的Car表
SELECT c.PlateNo, TicketStatus, EventTime
FROM
(
SELECT ROW_NUMBER() OVER
(PARTITION BY CarID ORDER BY EventTime) AS rnum
, CarID, TicketStatus, EventTime
FROM Event
) a JOIN Car c
ON a.CarID = c.CarID
WHERE c.Package = 4
AND a.rnum > 1
或者
SELECT PlateNo, TicketStatus, EventTime
FROM
(
SELECT e.CarID, e.TicketStatus, e.EventTime, c.PlateNo,
ROW_NUMBER() OVER
(PARTITION BY e.CarID ORDER BY e.EventTime) AS rnum
FROM Event e JOIN Car c
ON e.CarID = c.CarID
WHERE c.Package = 4
) a
WHERE a.rnum > 1
这是SQLFiddle演示
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句