代码之家 › 专栏 › 技术社区 › asmgx

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

sql-server sql

asmgx · 技术社区 · 4 年前

我有一张表,上面有库存中的所有项目,表名为词

词有两列( ProdID 和 Price ),看起来像这样

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

我还有一张文件桌,叫 Docs 有柱子 DocID , CustID 和 InvoiceID .

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 , 普罗迪德 , ProdSize .

在同一张发票上, 普罗迪德 可以重复,因为它可以在不同的大小

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

0 回复 | 直到 4 年前

George Joseph 4 年前

这里有一个方法可以做到这一点。我先过滤掉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

Popeye 4 年前

你可以使用 DENSE_RANK 详情如下:

select CustID, InvoiceID, ProdID, sum(qty) as qty 
  from (select d.CustID, d.InvoiceID, ci.ProdID, p.prodid as qty,
               dense_rank() over (order by ci.price desc) as rn
          from ci cross join docs d 
          left join purchase p on d.docid = p.docid and ci.prodid = p.prodid) t
  where rn <= 1000
group by CustID, InvoiceID, ProdID

Eralper 4 年前

您能试试下面的SQL Select语句吗?我在其中使用了公共表表达式 SQL CTEs

with topproducts as (
    select top 3 ProdID from CI order by Price desc
), sales as (
    select
        CustID,
        InvoiceID,
        ProdId,
        count(ProdId) as cnt
    from (
        select 
            d.CustID, 
            d.InvoiceID,
            p.ProdId
        from Docs d
        inner join Purchase p
            on p.DocID = d.DocID
        where p.ProdId in (select ProdId from topproducts)
    ) t1
    group by 
        CustID,
        InvoiceID,
        ProdId
)
select
    t.*, isnull(ss.cnt,0) as Qty
from (
select 
    distinct s.CustID, s.InvoiceID, p.ProdId
from sales s, topproducts p
) t
left join sales ss on ss.InvoiceID = t.InvoiceID and ss.ProdId = t.ProdId