我希望能夠比較同一張表中兩個不同日期的產品的收入

所以我可以像這樣運行兩個單獨的查詢：

SELECT date as date1, product as product1, product_id as product_id_1, SUM(revenue) AS rev1 
FROM product_inventory 
WHERE date = '2021-11-17' 
GROUP BY date1 , product1, product_id_1 
ORDER BY rev1 DESC

SELECT date as date2, product as product2, product_id as product_id_2, SUM(revenue) AS rev2 
FROM product_inventory 
WHERE date = '2022-11-17' 
GROUP BY date2 , product2, product_id_2 
ORDER BY rev2 DESC

這是我為每個人得到的輸出：

我怎樣才能以一種將 date2 和 rev2 列拉成這樣的單個輸出的方式查詢資料庫？

我試過這個查詢：

SELECT A.date1, A.product1, A.rev1, B.date2, B.product2, B.rev2 FROM
(
SELECT date as date1, product as product1, product_id as product_id_1, SUM(revenue) AS rev1 FROM product_inventory WHERE date = '2021-11-17' GROUP BY date1 , product1, product_id_1 ORDER BY rev1 DESC
) A,
(
SELECT date as date2, product as product2, product_id as product_id_2, SUM(revenue) AS rev2 FROM product_inventory WHERE date = '2022-11-17' GROUP BY date2, product2, product_id_2 ORDER BY rev2 DESC
) B;

但我得到了這個輸出

這就像記錄數的平方。

uj5u.com熱心網友回復：

您想要比較同一產品在兩個不同日期的收入。您可以在不使用子查詢或WITH使用條件聚合的情況下執行此操作：

select product_id, product,
    sum(case when date = '2021-11-17' then revenue end) as rev_2021_11_17,
    sum(case when date = '2022-11-17' then revenue end) as rev_2022_11_17
from product_inventory 
where date in ('2021-11-17', '2022-11-17')
group by product_id, product

我真的不認為需要在結果集中回傳日期；客戶已經知道它們，因為它們是作為查詢的引數提供的。但如果您愿意，您可以在SELECT子句中對它們進行硬編碼，或者使用MINand MAX。

uj5u.com熱心網友回復：

您需要像這樣在 product_id 上使用 JOIN：

WITH DATE_1 AS (
SELECT date as date1, product as product1, product_id as product_id_1, SUM(revenue) AS rev1 
  FROM product_inventory 
 WHERE date = '2021-10-17' 
 GROUP BY 1, 2, 3
),
DATE_2 AS (
SELECT date as date2, product as product2, product_id as product_id_2, SUM(revenue) AS rev2 
  FROM product_inventory 
 WHERE date = '2021-11-17' 
 GROUP BY 1, 2, 3
)
SELECT D1.*, D2.*
  FROM DATE_1 D1
       INNER JOIN DATE_2 D2
          ON D1.product_id_1 = D2.product_id_2

uj5u.com熱心網友回復：

您的前兩個查詢實際上是相同的。只有它們中的別名不同。這使得任務有點毫無意義。但是，如果我們假設您想要連接兩個結果集中的資料，那么您可以這樣做：

SELECT t1.date, t1.product, t1.product_id, t2.date, t2.product_id, ...
FROM
  (SELECT ... FROM product_inventory WHERE ... GROUP BY ...) AS t1
JOIN
  (SELECT ... FROM product_inventory WHERE ... GROUP BY ...) AS t2
ON t1.product_id = t2.product_id AND t1.date = t2.date
ORDER BY ...    

此外，MySQL 8 引入了一個名為Common Table Expressions (CTE)的功能，它可能是上述查詢的一個很好的替代方案，因為它允許您將要連接的兩個結果集的 SELECT 查詢分開。所以使用 CTE 你可以這樣寫：

WITH 
  t1 AS
  (SELECT ... FROM product_inventory WHERE ... GROUP BY ...),
  t2 AS
  (SELECT ... FROM product_inventory WHERE ... GROUP BY ...),
SELECT t1.date, t1.product, t1.product_id, t2.date, t2.product_id, ...
FROM t1      
JOIN t2 ON t1.product_id = t2.product_id AND t1.date = t2.date
ORDER BY ...

請注意，在這兩個查詢中，我都ORDER BY從兩個結果集中提取了 并將其放入“最終”SELECT。

uj5u.com熱心網友回復：

您正在獲得表 A 和 B 的笛卡爾連接，即 A 和 B 中行的所有組合，因此您會看到記錄增加到 9 行，因為 3 x 3。

您需要做的是在產品上連接表A和B：例如（內部）連接：

SELECT A.*, B.*
FROM A
INNER JOIN B on A.product = B.product

盡可能避免在 SELECT 中使用 *。

uj5u.com熱心網友回復：

使用你的資訊，你可以WITH從 sql 中執行，它會生成你可以用來做其他操作的“假表”

-- Your first query that I put in a "fake table" named first_date
WITH first_date AS (
    SELECT date as date1, product as product1, product_id as product_id_1, SUM(revenue) AS rev1 
    FROM product_inventory 
    WHERE date = '2021-11-17' 
    GROUP BY date1 , product1, product_id_1 
    ORDER BY rev1 DESC
),
-- Your second query that I put in a "fake table" named second_date
second_date AS (
    SELECT date as date2, product as product2, product_id as product_id_2, SUM(revenue) AS rev2 
    FROM product_inventory 
    WHERE date = '2022-11-17' 
    GROUP BY date2 , product2, product_id_2 
    ORDER BY rev2 DESC
)
-- First we get the products in both "fake tables"
SELECT a.*, b.date2, b.rev2
FROM first_date a
INNER JOIN second_date b ON a.product_id_1 = b.product_id_2
UNION
-- Then only in the first "fake table"
SELECT c.*, NULL, NULL
FROM first_date c
LEFT JOIN second_date d ON c.product_id_1 = d.product_id_2
WHERE d.product_id_2 IS NULL
UNION
-- Then only in the second "fake table"
SELECT NULL, f.product2, f.product_id_2, NULL, f.date2, f.rev2
FROM first_date e
RIGHT JOIN second_date f ON e.product_id_1 = f.product_id_2
WHERE e.product_id_1 IS NULL;

標籤：数据库数据库和子查询聚合函数

上一篇：JOIN子句SQL

下一篇：SQL-如何使用合并的SUM函式連接2個表？（當前結果不正確）