时间戳

首页 > 解决方案 > 如何在 SQL Server 中显示交叉联接表中所有项目的计数

sql - 如何在 SQL Server 中显示交叉联接表中所有项目的计数

问题描述

我有一个表，其中包含库存中的所有项目，表名为CI

CI有 2 列（ProdID和Price），看起来像这样

ProdID Price
-------------
A8373  700
G8745  900
J7363  300
K7222  800
Y6311  350

我有另一个表格，用于名为DocscolumnsDocID和.CustIDInvoiceID

DocID, CustID, InvoiceID
------------------------
1      1001    751
2      1001    752
3      1001    753
4      1002    831
5      1002    832
6      1003    901
7      1003    902

Purchase另一个用DocID, ProdID,调用的购买表ProdSize。

在同一张发票中，ProdID可以重复，因为它可以有不同的尺寸

DocID, ProdID, ProdSize
------------------------
1      A8373   41
1      A8373   42
1      A8373   43
1      G8745   35
1      G8745   36
2      A8373   44
2      A8373   45

现在我想获取所有客户和发票的产品数量，但对于价格最高的产品

所以应该是这样的

CustID, InvoiceID, ProdID, Quantity
-----------------------------------
1001    751        A8373   3
1001    751        G8745   2
1001    751        K7222   0
1001    752        A8373   2
1001    752        G8745   0
1001    752        K7222   0

并为该发票中不存在的产品显示 0

我写了这个查询，但它非常慢。我想知道是否有更简单的快速方法来获得这个结果

DECLARE @Features AS TABLE 
                     (
                         CustID varchar(100), 
                         InvoiceID varchar(100) 
                             INDEX IX3 CLUSTERED(CustID, InvoiceID), 
                         ProdID varchar(100), 
                         Quantity bigint
                     )
        
INSERT INTO @Features (CustID, InvoiceID, ProdID, Quantity)
    SELECT 
        R.CustID, R.InvoiceID, T.ProdID, COUNT(*) AS Quantity
    FROM 
        Docs R 
    CROSS JOIN 
        (SELECT TOP 1000 * FROM CIs ORDER BY Price DESC) C
    INNER JOIN 
        Purchase T ON T.DocID = R.DocID
    GROUP BY 
        R.CustID, R.InvoiceID, T.ProdID

SELECT TOP 100 * 
FROM @Features 
ORDER BY CustID, InvoiceID, ProdID

SELECT COUNT(*) FROM @Features

UPDATE F 
SET Quantity = Cnt
FROM @Features F 
INNER JOIN 
    (SELECT R.CustID, R.InvoiceID, COUNT(*) Cnt
     FROM Purchase T 
     INNER JOIN Docs R ON T.DocID = R.DocID
     GROUP BY R.CustID, R.InvoiceID ) X ON F.CustID = X.CustID 
                                        AND F.InvoiceID = X.InvoiceID

SELECT * FROM @Features

标签： sqlsql-server

解决方案

这是一种方法。我先过滤掉 1000 个产品，然后按如下方式执行连接。

也不需要更新查询，都可以在SQL本身中获取。

过滤早加入晚

with top_product
  as (select prodid,price, rownumber() over(order by price desc) as rnk
         from ci
      )
     ,invoice_product
     as(select d.docid,d.custid,d.invoiceid,p.prodid
          from top_product
          join docs d
            on 1=1
           and rnk<=1000
       ) 
   select a.CustID, a.InvoiceID, a.ProdID,count(b.prodid) as qty
     from invoice_product a
left join purchase b
       on a.DocID=b.docid
      and a.ProdID=b.prodid
 group by a.CustID, a.InvoiceID, a.ProdID

推荐阅读