在遇到重复行时，从子SELECT中使用结果集 [英] Using a Result Set From a Sub SELECT When Duplicate Rows are Encountered

问题描述

首先，我正在努力的表：

  DispatchLocations 
 ==== =========== 
 DispatchID int 
 StopNumber int 
到达的bool 
离开的bool 
  

想法是在货车路线上有一个调度的停靠点。这是每个调度上的每个位置的列表（实质上，它是一个路由表）。在这种观点中，每行输出只能有一个调度，指向调度的当前停止。

  SELECT TOP 4 
 DispatchID，
 Min（StopNumber）AS NextStop，
到达，
离开
 FROM DispatchLocations 
 GROUP BY 
 dispatchID，
到达，离开
 HAVING（Arrived = 0 OR Departed = 0）
 ORDER BY DispatchID ASC，NextStop ASC 
  

我的视图现在的输出：

  Dispatch_ID NextStop到达离开
 =========== ======== ======= ======== 
 3 2 False False 
 1356 2 False False 
 6410 1 True False 
 6410 2 False False 
  

最后由于GROUP BY必须包含到达和离开状态，打破了产出的独特性，因此排在一边。

可以使用非常相似的查询来提取所需的行（仅）：

  SELECT 
 DispatchID，
 Min（StopNumber）AS NextStop，
已到达，
离开
 FROM DispatchLocations 
 GROUP BY 
派发ID，
到达，离开
 HAVING 
（Arrived = 1 AND Departed = 0）AND Min（StopNumber）= 1 
 ORDER BY DispatchID ASC 
 
 Dispatch_ID NextStop到达离开
 = ========== ======== ======= ======== 
 6410 1 True False 
 50589 1 True False 
 50687 1 True False 
  

我通过从我的视图中选择所有DispatchID来验证这些结果 WHERE COUNT（DispatchID）> 1 。

如果在视图中遇到匹配的ID，那么如何使用第二个查询的结果？我不能得到 EXIST 或 EXCEPT 子句与 HAVING 存在于查询文本中。所以现在我把结果从数据库的手中拿出来，并通过任何重复的结果对应用程序进行逻辑排序，并且只保留每个调度ID遇到的第一个。但是我宁愿让数据库为我做这个。

更新

我正在使用SSMS 2008 ，它构建了默认情况下包含 TOP 100 PERCENT 的视图。以下是原始的 pastebin 。

解决方案

我想你想要这样的东西：

  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 
  

这将返回每个调度ID的最小停止号的详细信息。您可以通过简单地使用 WHERE 来进一步过滤。例如

  WHERE Arrived = 1 
 AND Departed = 0 
  pre> 
 
 
我认为将上面的选项添加到顶部的select语句会带来结果：
  Dispatch_ID NextStop到达离开
 =========== ======== ======= ====== 
 6410 1 True False 
 50589 1 True False 
 50687 1 True False 
  
尽管我可能完全误解了这个问题。
 
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.
解决方案
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.

                        
这篇关于在遇到重复行时，从子SELECT中使用结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！