T-SQL选择每个州最畅销的产品

问题描述：

我有多个产品销往全国各地。我将如何看到每个州的最畅销产品是什么？T-SQL选择每个州最畅销的产品

CREATE TABLE Customers (
CustomerID Int Not 
, Name Varchar(75) 
, UserName Varchar(75) 
, Password Varchar(75) 
, Email Varchar(75) 
, PrimaryPhone Varchar(50) 
, Primary Key (CustomerID) 
) 

CREATE TABLE ShippingAddress (
AddressID Int Primary Key 
, CustomerID Int 
, Street (75) 
, State Varchar (75) 
, Zip Varchar (75) 
,Foreign Key (CustomerID) REFERENCES Customers 
) 

CREATE TABLE PaymentInfo (
CreditCardNumber Int Primary Key 
, CardExpDate Date 
, CVVCode Int 
, CustomerID Int 
, AddressID Int 
, Foreign Key (CustomerID) REFERENCES Customers 
, Foreign Key (AddressID) REFERENCES Addresses 
) 

CREATE TABLE Orders (
OrderID Int Primary Key 
, OrderDate Date 
, CustomerID Int 
, TotalPrice Int 
, Foreign Key (CustomerID) REFERENCES Customers 
) 

CREATE TABLE Shipping (
ShippingID Int Primary Key 
, OrderID Int 
, AddressID Int 
, Foreign Key (OrderID) REFERENCES Orders 
, Foreign Key (AddressID) REFERENCES Addresses 
) 

CREATE TABLE OrderPayment (
OrderID Int 
, CreditCardNumber Int 
, Primary Key (OrderID, CreditCardNumber) 
, Foreign Key (OrderID) REFERENCES Orders 
, Foreign Key (CreditCardNumber) REFERENCES PaymentInfo 
) 

CREATE TABLE Inventory (
ItemModelNumber Int Primary Key 
, Brand Int 
, ItemDescription Text 
, StockQuantity Int 
) 

CREATE TABLE OrderItems (
OrderID Int 
, ItemModelNumber Int 
, Primary Key (OrderID, ItemModelNumber) 
, Foreign Key (OrderID) REFERENCES Orders 
, Foreign Key (ItemModelNumber) REFERENCES Inventory 
) 


CREATE TABLE Sources (
DistributorID Int Primary Key 
, DistributorName Varchar(75) 
) 

CREATE TABLE InventorySource (
ItemModelNumber Int 
, DistributorID Int 
, RequestedDate Date 
, DeliveredDate Date 
, RequestedQuantity Int 
, Primary key (ItemModelNumber, DistributorID) 
, Foreign Key (ItemModelNumber) REFERENCES Inventory 
, Foreign Key (DistributorID) REFERENCES Sources 
) 

GO 


CREATE VIEW BestSellers AS(

所以我知道我想从ShippingAddress中选择DISTINCT状态。然后，我想我需要从OrderItems表中计数（ItemModelNumber），OrderID，并将这些列与ShippingAddress表中的Distinct State列进行连接。然后在状态上只是一个GROUP BY声明..到目前为止这是否正确？

逻辑是正确的，但在哪里查询定义您的逻辑？ – 2015-03-19 07:01:37

所有这些表都与您的查询有关吗？如果不是，那么不要显示它们，因为它只会增加你所问问题的噪音。 – sr28 2015-03-19 11:47:36

在我看来，这里可能有些遗漏。我在哪里可以看到每件订单订购了多少件物品以及价格是多少。此外，我有点朦胧你的意思是“最畅销”。是创造收入最多的项目，订单最多的项目还是发货量最大的项目？ – Ralph 2015-03-20 15:38:36

答

我觉得下面的查询提供每个国家最好的项目

注：我使用COUNT（*），因为我没有找到订单

SELECT best.* 
FROM (
     SELECT sales.* 
      , I.ItemDescription 
      , ROW_NUMBER() OVER(PATITION BY sales.State ORDER BY sales.quantity DESC) AS sort 
     FROM (
       SELECT SA.State 
        , OI.ItemModelNumber 
        , COUNT(*) AS quantity 
       FROM OrderItems OI 
        INNER JOIN 
         Shipping S 
         ON OI.OrderID = S.OrderID 
        INNER JOIN 
         ShippingAddress SA 
         ON S.AddressID = SA.AddressID 
       GROUP BY SA.State 
        , OI.ItemModelNumber 
       ) sales 
      INNER JOIN 
       Inventory I 
       ON sales.ItemModelNumber = I.Inventory 
     ) best 
WHERE best.sort = 1

我希望这是一个“数量”字段帮助

Regards

T-SQL选择每个州最畅销的产品

相关推荐