将这两个查询合并为一个查询

Question

我有以下查询：

SELECT Sites.EDISID, Sites.[Name], (SUM(DLData.Quantity) / 8) AS TiedDispense
FROM Sites
  JOIN UserSites 
    ON UserSites.EDISID = Sites.EDISID
  JOIN Users
    ON Users.[ID] = UserSites.UserID
 JOIN MasterDates 
  ON MasterDates.EDISID = UserSites.EDISID
 JOIN DLData
  ON DLData.DownloadID = MasterDates.[ID]
 JOIN Products 
  ON Products.[ID] = DLData.Product
 LEFT JOIN SiteProductTies 
  ON SiteProductTies.EDISID = UserSites.EDISID
  AND SiteProductTies.ProductID = Products.[ID]
 LEFT JOIN SiteProductCategoryTies 
  ON SiteProductCategoryTies.EDISID = UserSites.EDISID
  AND SiteProductCategoryTies.ProductCategoryID = Products.CategoryID
 WHERE Users.[ID] = @UserID
  AND (COALESCE(SiteProductTies.Tied, SiteProductCategoryTies.Tied, Products.Tied) = @Tied OR @Tied IS NULL)
  AND MasterDates.[Date] BETWEEN @From AND @To
  AND MasterDates.[Date] >= Sites.SiteOnline
 GROUP BY Sites.EDISID, Sites.[Name]

SELECT Sites.EDISID, Sites.[Name], SUM(Delivery.Quantity) AS TiedDelivered
FROM Sites
  JOIN UserSites 
    ON UserSites.EDISID = Sites.EDISID
  JOIN Users
    ON Users.[ID] = UserSites.UserID
 JOIN MasterDates 
  ON MasterDates.EDISID = UserSites.EDISID
 JOIN Delivery
  ON Delivery.DeliveryID = MasterDates.[ID]
 JOIN Products 
  ON Products.[ID] = Delivery.Product
 LEFT JOIN SiteProductTies 
  ON SiteProductTies.EDISID = UserSites.EDISID
  AND SiteProductTies.ProductID = Products.[ID]
 LEFT JOIN SiteProductCategoryTies 
  ON SiteProductCategoryTies.EDISID = UserSites.EDISID
  AND SiteProductCategoryTies.ProductCategoryID = Products.CategoryID
 WHERE Users.[ID] = @UserID
  AND (COALESCE(SiteProductTies.Tied, SiteProductCategoryTies.Tied, Products.Tied) = @Tied OR @Tied IS NULL)
  AND MasterDates.[Date] BETWEEN @From AND @To
  AND MasterDates.[Date] >= Sites.SiteOnline
 GROUP BY Sites.EDISID, Sites.[Name]

如您所见，它们非常相似 - 只有有关查询是针对 DLData 还是 Delivery 的行不同。一个返回交付的总数，另一个返回分配的总数。

目前我在第三个查询中将它们用作两个单独的子查询。每个单独花费大约 1-2 秒。作为两个子查询，它们需要 6 到 10 秒的时间（取决于负载），并且都只返回 47 行（尽管它们总共涉及数千行）。

我认为将它们结合起来会给我带来相当大的速度 - 特别是因为这个查询将被多次调用。

然而，当我尝试将两者结合起来时，由于行数发生了变化，我的尝试失败了。我尝试了各种 JOIN 组合，但没有返回正确的结果。

SO们有什么建议吗？

Answer 1

您可以尝试：

SELECT Sites.EDISID, 
    Sites.[Name], 
    (SUM(DLData.Quantity) / 8) AS TiedDispense, 
    SUM(Delivery.Quantity) AS TiedDelivered
FROM Sites  
JOIN UserSites     
    ON UserSites.EDISID = Sites.EDISID  
JOIN Users    
    ON Users.[ID] = UserSites.UserID 
JOIN MasterDates   
    ON MasterDates.EDISID = UserSites.EDISID 
JOIN DLData  
    ON DLData.DownloadID = MasterDates.[ID] 
JOIN Products   
    ON Products.[ID] = DLData.Product
LEFT JOIN Delivery  
    ON Delivery.DeliveryID = MasterDates.[ID] 
LEFT JOIN SiteProductTies   
    ON SiteProductTies.EDISID = UserSites.EDISID  AND SiteProductTies.ProductID = Products.[ID] 
LEFT JOIN SiteProductCategoryTies   
    ON SiteProductCategoryTies.EDISID = UserSites.EDISID  
    AND SiteProductCategoryTies.ProductCategoryID = Products.CategoryID 
WHERE Users.[ID] = @UserID  
    AND (COALESCE(SiteProductTies.Tied, SiteProductCategoryTies.Tied, Products.Tied) = @Tied 
        OR @Tied IS NULL)  
    AND MasterDates.[Date] BETWEEN @From AND @To  
    AND MasterDates.[Date] >= Sites.SiteOnline 
GROUP BY Sites.EDISID, Sites.[Name]

我做了左连接，但内部连接可能会起作用，具体取决于您的数据。我还会检查以确保所有这些外键字段都已建立索引。

Answer 2

快速查看您的查询后，如果不了解数据背后的业务规则，我无法确定这是否正确。但是，如果您愿意，可以尝试一下：

SELECT 

Sites.EDISID, Sites.[Name], 
CASE WHEN Delivery.DeliveryID IS NULL THEN 0 ELSE SUM(Delivery.Quantity) END TiedDelivered, 
CASE WHEN DLData.[ID] IS NULL THEN 0 ELSE (SUM(DLData.Quantity) / 8) END TiedDispense 

FROM Sites
  JOIN UserSites 
    ON UserSites.EDISID = Sites.EDISID
  JOIN Users
    ON Users.[ID] = UserSites.UserID
 JOIN MasterDates 
  ON MasterDates.EDISID = UserSites.EDISID
 LEFT JOIN Products 
  ON Products.[ID] = DLData.Product

 LEFT JOIN DLData
  ON DLData.DownloadID = MasterDates.[ID]
 LEFT JOIN Delivery
  ON Delivery.DeliveryID = MasterDates.[ID]

 LEFT JOIN SiteProductTies 
  ON SiteProductTies.EDISID = UserSites.EDISID
  AND SiteProductTies.ProductID = Products.[ID]
 LEFT JOIN SiteProductCategoryTies 
  ON SiteProductCategoryTies.EDISID = UserSites.EDISID
  AND SiteProductCategoryTies.ProductCategoryID = Products.CategoryID
 WHERE Users.[ID] = @UserID
  AND (COALESCE(SiteProductTies.Tied, SiteProductCategoryTies.Tied, Products.Tied) = @Tied OR @Tied IS NULL)
  AND MasterDates.[Date] BETWEEN @From AND @To
  AND MasterDates.[Date] >= Sites.SiteOnline
  AND (DLData.[DownloadID] IS NOT NULL OR DELIVERY.DeliveryID IS NOT NULL)
 GROUP BY Sites.EDISID, Sites.[Name]

其中一个关键是这部分：

  AND (DLData.[DownloadID] IS NOT NULL OR DELIVERY.DeliveryID IS NOT NULL)

这在很大程度上基于业务规则的假设，但可能会弥补两个左连接返回的额外行。如果您愿意，您也可以玩这样的东西：

AND ( TiedDelivered != 0 AND TiedDispense != 0)

希望这会有所帮助。

-史蒂夫

Answer 3

我对此写了一个愚蠢的答案，它让我烦恼，所以我开始更多地研究它 - 基本上你想将分组放在连接内。我没有时间编辑您的代码，但我认为这个示例应该可以帮助您：

create table #prod(
prodid int,
prodamount int)

create table #del(
delid int,
delamount int)

create table #main(
id int,
name varchar(50))

insert into #main(id,name)
select 1, 'test 1'
union select 2, 'test 2'
union select 3, 'test 3'
union select 4, 'test 4'

insert into #prod(prodid,prodamount)
select 1, 10
union select 1, 20
union select 1, 30
union select 2, 5

insert into #del(delid,delamount)
select 1, 9
union select 1, 8
union select 3, 7

/** wrong **/

select m.id, m.name, isnull(sum(p.prodamount),0), isnull(sum(d.delamount),0)
from #main m
left join #prod p on p.prodid = m.id
left join #del d on d.delid = m.id
group by m.id, m.name

/** right! **/
select id, name, isnull(myprod.prodtot,0) as prodtot, isnull(mydel.deltot,0) as deltot
from #main
left join 
    (SELECT prodid, SUM(prodamount) AS prodtot
    FROM #prod
    GROUP BY prodid) myprod on #main.id = myprod.prodid
left join 
    (SELECT delid, SUM(delamount) AS deltot
    FROM #del
    GROUP BY delid) mydel on #main.id = mydel.delid



drop table #prod
drop table #del
drop table #main

Answer 4

以下是将您的查询重写为单个查询：

   SELECT t.edisid,
          t.name, 
          SUM(dd.quantity) / 8 AS TiedDispense,
          SUM(d.quantity) AS TiedDelivered
     FROM SITES t
     JOIN USERSITES us ON us.edisid = t.esisid
     JOIN USERS u ON u.id = us.userid
     JOIN MASTERDATES md ON md.edisid = us.edisid
                        AND md.date >= t.siteonline
LEFT JOIN DLDATA dd ON dd.downloadid = md.id
LEFT JOIN DELIVERY d ON d.deliveryid = md.id
     JOIN PRODUCTS p ON p.id IN (dd.product, d.product)
LEFT JOIN SITEPRODUCTTIES spt ON spt.edisid = us.edisid
                             AND spt.productid = p.id
LEFT JOIN SITEPRODUCTCATEGORYTIES spct ON spct.edisid = us.edisid
                                      AND spct.productcategoryid = p.categoryid
    WHERE u.id = @UserID
      AND (@Tied IS NULL OR COALESCE(spt.tied, spct.tied, p.tied) = @Tied)
      AND md.date BETWEEN @From AND @To      
 GROUP BY t.edisid, t.name

根据您的数据，DLDATA 和 DELIVERY 的 JOIN 可能是内部联接。

养成使用表别名的习惯会很好。

Answer 5

不想听起来过于愚蠢，但我想联合不会有帮助（需要对返回的列名进行一些小的更改......）？