时间戳

首页 > 解决方案 > 使用内连接选择出现次数最多的 SQL

sql - 使用内连接选择出现次数最多的 SQL

问题描述

我正在使用此查询从不同的链接表中获取以下数据。但是，假设VENDORS一个项目是三个。现在在这里结果我想显示发生最多的供应商。我的意思是，如果 ItemABC多次由 3 个不同的供应商提供。然后在这里我想得到提供大部分时间项目 ABC 的供应商。

我的查询是这样的。 

use iBusinessFlex;
SELECT Items.Name, 
Max(Items.ItemID) as ItemID ,
MAX(Items.Description)as Description,
MAX(ItemsStock.CurrentPrice) as UnitPrice,
MAX(ItemsStock.Quantity) as StockQuantiity,
MAX(Vendors.VendorName) as VendorName, 
SUM(ItemReceived.Quantity) as TotalQuantity
From ItemReceived  
INNER JOIN Items ON ItemReceived.ItemId=Items.ItemID 
INNER JOIN ItemsStock ON ItemReceived.ItemId=ItemsStock.ItemID 
INNER JOIN PurchaseInvoices ON PurchaseInvoices.PurchaseInvoiceId = ItemReceived.PurchaseInvoiceId 
INNER JOIN Vendors ON Vendors.VendorId = PurchaseInvoices.VendorId
Group By Items.Name

编辑：我已经包含了这个子查询，但我不确定它是否显示正确的结果。我的意思是为大多数时间提供该项目的每个项目显示供应商 

use iBusinessFlex;
SELECT Items.Name,
Max(Items.ItemID) as ItemID ,
MAX(Items.Description)as Description,MAX(ItemsStock.CurrentPrice) as UnitPrice,
MAX(ItemsStock.Quantity) as StockQuantiity,MAX(Vendors.VendorName) as VendorName, 
SUM(ItemReceived.Quantity) as TotalQuantity
From ItemReceived  
INNER JOIN Items ON ItemReceived.ItemId=Items.ItemID INNER JOIN ItemsStock 
ON ItemReceived.ItemId=ItemsStock.ItemID INNER JOIN PurchaseInvoices 
ON PurchaseInvoices.PurchaseInvoiceId = ItemReceived.PurchaseInvoiceId INNER JOIN Vendors
ON Vendors.VendorId IN (
SELECT Top 1 MAX(PurchaseInvoices.VendorId) as VendorOccur
FROM PurchaseInvoices INNER JOIN Vendors ON Vendors.VendorId=PurchaseInvoices.VendorId
GROUP BY PurchaseInvoices.VendorId
ORDER BY COUNT(*) DESC

结果看起来像这样。
此查询的结果图像

供应商表

标签： sqlsql-server

解决方案

First, I would start with who ordered what thing the most. But the MOST is based on what... the most quantity? Price?, Number of Times? If you use one vendor and order 6 times qty of 10 you have 60 things. But order 1 time from another vendor for 100 qty, which one wins. You have to decide the basis of MOST, but I will go based on most times per your original question.

So all things come from PurchasedInvoices which has a vendor ID. I dont care who the vendor is, just their ID, so no need to join. Also, don't need the item name if I am just looking for my counts. The query below will show per item, each vendor and their respective most times ordered and quantities ordered. I added the items and vendor table joins just to show the names.

select
        IR.ItemID,
        PI.VendorID,
        max( I.Name ) Name,
        max( V.VendorName ) VendorName,
        count(*) as TimesOrderedFrom,
        SUM( IR.Quantity ) as QuantityFromVendor
    from
        ItemsReceived IR
            JOIN PurchaseInvoices PI
                on IR.PurchaseInvoiceID = PI.PurchaseInvoiceID
            JOIN Items I
                on IR.ItemID = I.ItemID
            JOIN Vendors V
                on IR.VendorID = V.VendorID
    group by
        IR.ItemID,
        PI.VendorID
    order by
        -- Per item
        IR.ItemID,
        -- Most count ordered
        count(*),
        -- If multiple vendors, same count, get total quantity
        sum( IR.Quantity )

Now, to get only 1 per item, this would create a correlated subquery and you can add 'TOP 1' to return only the first by this. Since the aggregate of count is already done, you can then get the vendor contact info. 

select
        I.Name,
        V.VendorName,
        TopVendor.TimesOrderedFromVendor,
        TopVendor.QuantityFromVendor
    from
        Items I
            JOIN ( select TOP 1
                            IR.ItemID,
                            PI.VendorID,
                            count(*) as TimesOrderedFrom,
                            SUM( IR.Quantity ) as QuantityFromVendor
                        from
                            ItemsReceived IR
                                JOIN PurchaseInvoices PI
                                    on IR.PurchaseInvoiceID = PI.PurchaseInvoiceID
                        where
                            -- correlated subquery based on the outer-most item
                            IR.ItemID = I.ItemID
                        group by
                            IR.ItemID,
                            PI.VendorID
                        order by
                            -- Per item
                            IR.ItemID,
                            -- Most count ordered
                            count(*),
                            -- If multiple vendors, same count, get total quantity
                            sum( IR.Quantity ) ) TopVendor
                on I.ItemID = TopVendor.ItemID
                JOIN Vendors V
                    on TopVendor.VendorID = V.VendorID

No sense in having the INNER Subquery joining on the vendor and items just for the names. Get those once and only at the end when the top vendor is selected.

推荐阅读