显示2009年销售额排名第二的制造商和2010年销售额排名第一的制造商

Question

我有6个6表中的数据，我已经连接了3个表。我想在两个不同的列中返回一个输出。

这就是我正在做的事情，但却出了问题。

SELECT  
(SELECT 
TOP 2 Manufacturer_name
FROM Fact_Transactions FT
LEFT JOIN DIM_Model DM ON FT.IDModel = DM.IDModel
LEFT JOIN DIM_MANUFACTURER MFC  ON MFC.IDManufacturer = DM.IDManufacturer
Where DATEPART(Year,date)='2009' 
group by Manufacturer_name 
Order by SUM (TotalPrice) ) as A,

(SELECT 
Top 2 Manufacturer_name
FROM Fact_Transactions FT
LEFT JOIN DIM_Model DM ON FT.IDModel = DM.IDModel
LEFT JOIN DIM_MANUFACTURER MFC  ON MFC.IDManufacturer = DM.IDManufacturer
Where DATEPART(Year,date)='2010' 
group by Manufacturer_name
Order by SUM (TotalPrice)) as B

/*这显示错误=>子查询返回的值超过1。当子查询跟在=、！=、<lt；=，>，>=或者当子查询用作表达式时。*/

CREATE TABLE DIM_MANUFACTURER (
IDManufacturer INT PRIMARY KEY IDENTITY(11, 1),
Manufacturer_Name VARCHAR (20)
)
CREATE TABLE DIM_MODEL (
IDModel INT PRIMARY KEY IDENTITY(101, 1),
Model_Name VARCHAR(20),
Unit_price MONEY ,
IDManufacturer INT REFERENCES DIM_Manufacturer(IDManufacturer)
)
CREATE TABLE FACT_TRANSACTIONS (
IDModel INT REFERENCES DIM_MODEL(IDModel),
IDCustomer INT REFERENCES DIM_CUSTOMER(IDCustomer),
IDLocation INT REFERENCES DIM_LOCATION(IDLocation),
Date DATE REFERENCES DIM_DATE([DATE]),
TotalPrice MONEY,
Quantity INT,
)
INSERT INTO DIM_Manufacturer VALUES 
('Apple'), ('Samsung'), ('One Plus'), ('Nokia'), ('Motorola'), ('HTC')
INSERT INTO DIM_MODEL VALUES
('iPhone 4', 377, '11'), 
('iPhone 4S', 414, '11'), 
('iPhone 5', 456, '11'), 
('iPhone 6', 501, '11'), 
('iPhone 7', 552, '11'), 
('Thunderbolt', 201, '16'), 
('C139', 121, '15'), ......

INSERT INTO FACT_TRANSACTIONS VALUES
(130, 10044, 2001, '05/25/2009', 500, 1), 
(107, 10043, 2001, '04/16/2003', 126, 1), 
(126, 10022, 2005, '11/03/2003', 169, 1), 
(111, 10045, 2003, '01/01/2010', 286, 1), 
(118, 10012, 2010, '04/21/2007', 149, 1), 
(128, 10044, 2005, '10/19/2010', 318, 1), 
(124, 10003, 2009, '10/03/2010', 435, 1), 
(117, 10002, 2003, '10/13/2010', 54, 1), .....

Answer 1

在这种情况下，我认为DENSE_RANK((和CTE将非常有用。这里有一个解决方案：

WITH cte AS
(
SELECT Manufacturer_name, DATEPART(Year,date) as yr,
DENSE_RANK() OVER (PARTITION BY DATEPART(Year,date) ORDER BY SUM(TotalPrice) DESC) AS Rank 
FROM Fact_Transactions FT
LEFT JOIN DIM_Model DM ON FT.IDModel = DM.IDModel
LEFT JOIN DIM_MANUFACTURER MFC  ON MFC.IDManufacturer = DM.IDManufacturer
group by Manufacturer_name,DATEPART(Year,date) 
),
cte2 AS(
SELECT Manufacturer_Name, yr
FROM cte WHERE rank = 2
AND yr IN ('2009','2010')
)
SELECT c.Manufacturer_Name AS Manufacturer_Name_2009
,t.Manufacturer_Name AS Manufacturer_Name_2010
FROM cte2 AS c, cte2 AS t
WHERE c.yr < t.yr;

Answer 2

您可以尝试使用row_number()

select X.Manufacturer_name as Manufacturer_name_09,Y.Manufacturer_name as Manufacturer_name_10
from 
(
SELECT Manufacturer_name,row_number() over(order by SUM (TotalPrice)) as rn1
FROM Fact_Transactions FT
LEFT JOIN DIM_Model DM ON FT.IDModel = DM.IDModel
LEFT JOIN DIM_MANUFACTURER MFC  ON MFC.IDManufacturer = DM.IDManufacturer
Where DATEPART(Year,date)=2009
group by Manufacturer_name 
)X inner join
(
SELECT Manufacturer_name,row_number() over(order by SUM (TotalPrice)) as rn2
FROM Fact_Transactions FT
LEFT JOIN DIM_Model DM ON FT.IDModel = DM.IDModel
LEFT JOIN DIM_MANUFACTURER MFC  ON MFC.IDManufacturer = DM.IDManufacturer
Where DATEPART(Year,date)=2010 
group by Manufacturer_name 
)Y on X.rn1=Y.rn2  and rn1 in (1,2) and rn2 in (1,2)

Answer 3

使用此

SELECT  top 1 * 
from
(SELECT 
TOP 2 Manufacturer_name,
SUM(Quantity )  TQ1
FROM Fact_Transactions T1
LEFT JOIN DIM_Model D1 ON T1.IDModel = D1.IDModel
LEFT JOIN DIM_MANUFACTURER D2  ON D2.IDManufacturer = D1.IDManufacturer
Where DATEPART(Year,date)='2009' 
group by Manufacturer_name, Quantity 
Order by  SUM(Quantity ) DESC ) as A,

(SELECT 
Top 2 Manufacturer_name,
SUM(Quantity ) TQ2
FROM Fact_Transactions T2
LEFT JOIN DIM_Model DM ON T2.IDModel = DM.IDModel
LEFT JOIN DIM_MANUFACTURER DM2  ON DM2.IDManufacturer = DM.IDManufacturer
Where DATEPART(Year,date)='2010' 
group by Manufacturer_name,Quantity
Order by  SUM(Quantity )DESC ) as B

Answer 4

WITH RANK1 AS 
(
SELECT MANUFACTURER_NAME,YEAR(DATE) AS YEAR,
DENSE_RANK() OVER (PARTITION BY YEAR(DATE) ORDER BY SUM(TOTALPRICE)DESC) AS RANK
FROM FACT_TRANSACTIONS AS T1
INNER JOIN DIM_MODEL AS T2
ON T1.IDMODEL = T2.IDModel
INNER JOIN DIM_MANUFACTURER AS T3
ON T3.IDManufacturer = T2.IDManufacturer
GROUP BY Manufacturer_Name, YEAR(DATE)
)
SELECT YEAR, MANUFACTURER_NAME
FROM RANK1
WHERE YEAR IN ('2009','2010') AND RANK='2'