Using a Result Set From a Sub SELECT When Duplicat

问题:

First of all, the table I'm struggling with:

DispatchLocations
=================
DispatchID        int
StopNumber        int
Arrived           bool
Departed          bool

The idea is that on a trucking route, there are many stops in a dispatch. This is a list of each location on each dispatch (essentially, it's a route table). In this view, there should only be one dispatch for each row of output, which points to the "current stop" that the dispatch is at.

SELECT TOP 4
  DispatchID,
  Min(StopNumber) AS NextStop,
  Arrived,
  Departed
FROM DispatchLocations
GROUP BY 
  DispatchID,
  Arrived, Departed
HAVING (Arrived = 0 OR Departed = 0)
ORDER BY DispatchID ASC, NextStop ASC

My view's output as of now:

Dispatch_ID    NextStop    Arrived    Departed
===========    ========    =======    ========
3              2           False      False
1356           2           False      False
6410           1           True       False
6410           2           False      False

The last row is being dumped in because the GROUP BY must include the arrival and departure status, breaking the uniqueness of the output.

The desired rows (only) can be extracted using a very similar query:

SELECT
  DispatchID,
  Min(StopNumber) AS NextStop,
  Arrived,
  Departed
FROM DispatchLocations
GROUP BY 
  DispatchID,
  Arrived, Departed
HAVING
  (Arrived = 1 AND Departed = 0) AND Min(StopNumber) = 1
ORDER BY DispatchID ASC

Dispatch_ID    NextStop    Arrived    Departed
===========    ========    =======    ========
6410           1           True       False
50589          1           True       False
50687          1           True       False

I verified these results by selecting all DispatchIDs from my view WHERE COUNT(DispatchID) > 1.

How can I use the results from the second query whenever I encounter those matching IDs in the view? I can't get an EXIST or an EXCEPT clause to work with HAVING present in the query text. So for now I'm taking the results set off the database's hands, and having the application logic sort through any duplicate results, and keeping only the first one encountered for each dispatch ID. But I'd rather have the database do this for me.

UPDATE

I'm using SSMS 2008, which builds views with a TOP 100 PERCENT included by default. Here's the pastebin of the original.

回答1:

I think you want something like this:

SELECT  dl.DispatchID,
        dl.StopNumber AS NextStop,
        dl.Arrived,
        dl.Departed
FROM    DispatchLocations dl
        INNER JOIN
        (   SELECT  DispatchID, MIN(StopNumber) [StopNumber]
            FROM    DispatchLocations
            GROUP BY DispatchID
        ) MinDL
            ON MinDL.DispatchID = dl.DispatchID
            AND MinDL.StopNumber = dl.StopNumber

This will return the details for the lowest stopnumber for each dispatch ID. You can then filter this further by simply using WHERE. E.g.

WHERE  Arrived = 1
AND    Departed = 0

I think adding the above to the select statement at the top will bring back the results:

Dispatch_ID    NextStop    Arrived    Departed
===========    ========    =======    ========
6410           1           True       False
50589          1           True       False
50687          1           True       False

Although I may have completely misunderstood the question.