如何在MYSQL中使用JOIN和/或UNION計算不同表中的值？

我想一起計算product_name這些表中的：

jan_product                feb_product
 ------------               ------------ 
|product_name|             |product_name|
 ------------               ------------ 
|A           |             |A           |
 ------------               ------------ 
|A           |             |B           |
 ------------               ------------ 
|C           |             |C           |
 ------------               ------------ 

我希望我的結果看起來像：

 ------------ --------- --------- 
|product_name|jan_count|feb_count|
 ------------ --------- --------- 
|A           |2        |1        |
 ------------ --------- --------- 
|B           |0        |1        |
 ------------ --------- --------- 
|C           |1        |1        |
 ------------ --------- --------- 

所以我嘗試了下面的查詢（我使用的是 MYSQL，所以我無法嘗試 FULL JOIN）：

SELECT 
    j.product_name, 
    count(j.product_name) as jan_count,
    count(f.product_name) as feb_count
FROM jan_product as j  
JOIN feb_product as f
ON j.product_name = f.product_name
group by j.product_name

UNION

SELECT 
    f.product_name, 
    count(j.product_name) as jan_count,
    count(f.product_name) as feb_count
FROM jan_product as j  
RIGHT OUTER JOIN feb_product as f
ON f.product_name = j.product_name
group by f.product_name
; 

但我得到了這個：

 ------------ --------- --------- 
|product_name|jan_count|feb_count|
 ------------ --------- --------- 
|A           |2        |2        | --- A counts for FEB is wrong
 ------------ --------- --------- 
|B           |0        |1        |
 ------------ --------- --------- 
|C           |1        |1        |
 ------------ --------- --------- 

我不知道該怎么做才能達到預期的結果。

uj5u.com熱心網友回復：

我們可以使用 Union all 中的 count 來做到這一點。我寧愿建議您有一張帶有月份列的表格。

create table jan (pname char(1));
create table feb (pname char(1));
insert into jan values('A'),('A'),('C');
insert into feb values ('A'),('B'),('C');
select
  pname, 
  count(j) jan,
  count(f) feb
from
  (select pname,pname j,null f from jan
    union all
  select pname,null,pname from feb) jf
group by pname

名稱 | 一月 | 二月
:---- | --: | --:
一個 | 2 | 1
乙| 0 | 1
C | 1 | 1

db<>在這里擺弄

uj5u.com熱心網友回復：

忽略表結構一分鐘...嘗試使用 UNION ALL，包括一個額外的列來指示源月份。然后使用條件 SUM 計算每個月的總數。

參見db<>fiddle

SELECT  t.product_name
        , SUM( CASE WHEN t.month_number = 1 THEN 1 ELSE 0 END) AS jan_count
        , SUM( CASE WHEN t.month_number = 2 THEN 1 ELSE 0 END) AS feb_count
FROM   (
           SELECT CAST(1 AS UNSIGNED) AS month_number, product_name
           FROM   jan_product
           UNION ALL 
           SELECT CAST(2 AS UNSIGNED) AS month_number, product_name
           FROM   feb_product
       ) t      
GROUP BY t.product_name  

結果：

產品名稱 | jan_count | feb_count
:----------- | --------: | --------:
一個 | 2 | 1
C | 1 | 1
乙| 0 | 1

話雖如此，您應該規范化模型。您可以通過將所有內容存盤在一個帶有日期（或月份 年份列）的表中來大大簡化事情，而不是每個月都有一個單獨的表。

此外，您似乎正在存盤有關特定產品隨時間發生的事件的資訊。如果是這種情況，您應該有一個包含獨特產品的單獨表格：

存盤有關產品資訊的其他表應存盤“產品”表的唯一 PK（主鍵）值 -而不是產品名稱。例如，如果您有一個 ProductSales 表

 | ProductId | SaleDate    | Quantity |
 |-----------|-------------|----------|
 |   1       | 02/01/2022  | 15       |
 |   2       | 02/10/2022  | 4        |
 |   1       | 02/12/2022  | 3        |
 |   3       | 02/01/2022  | 20       |

要按月檢索有關銷售的資訊，您只需要在兩個表之間進行簡單的 JOIN

參見db<>fiddle

SELECT p.product_name
       , year(s.sales_date) AS sales_year
       , SUM( CASE month(s.sales_date) WHEN 1 THEN 1 ELSE 0 END) AS jan_sales
       , SUM( CASE month(s.sales_date) WHEN 2 THEN 1 ELSE 0 END) AS feb_sales
       , SUM( CASE month(s.sales_date) WHEN 3 THEN 1 ELSE 0 END) AS mar_sales
       -- ... etc
FROM   product p LEFT JOIN product_sales s ON s.product_id = p.product_id
GROUP BY p.product_name
        , year(s.sales_date) 
;

結果：

產品名稱 | 銷售年 | jan_sales | feb_sales | mar_sales
:----------- | ---------: | --------: | --------: | --------:
產品A | 2022 | 0 | 2 | 0
產品B | 2022 | 0 | 1 | 0
產品C | 2022 | 0 | 1 | 0

uj5u.com熱心網友回復：

--Please try using below query
-------
WITH cte AS
           ( SELECT * FROM jan_product
             UNION
             SELECT * FROM feb_product)

SELECT cte.product_name,
       j.Jan_count,
       count(f.product_name) as February_count
FROM cte
LEFT JOIN (SELECT product_name,
           COUNT(product_name) as Jan_count
           FROM jan_product
           GROUP BY product_name) j
ON cte.product_name=j.product_name
LEFT JOIN (SELECT product_name,
           COUNT(product_name) as Feb_count
           FROM feb_product
           GROUP BY product_name) f
ON cte.product_name=f.product_name

標籤：mysql sql 加入 联盟

上一篇：MySQL-計算2個最接近的記錄之間的平均時間差

下一篇：找到使數量最大化的列組合