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

Question

我想将这些表中的 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        |
+------------+---------+---------+

我不知道该怎么做才能达到预期的结果。

Answer 1

我们可以通过 Union all 的计数来做到这一点。我宁愿建议您使用一张带有月份列的表格。

create table jan (pname char(1));
创建二月表 (pname char(1));
插入 jan 值('A'),('A'),('C');
插入二月值 ('A'),('B'),('C');
选择
  姓名， 
  一月计数（j），
  2 月计数 (f)
从
  （从 jan 中选择 pname、pname j、null f
    联合所有
  从二月份选择 pname,null,pname) jf
按 pname 分组

<前>
姓名 |一月 |二月
:---- | --: | --:
一个 | 2 | 1
乙| 0 | 1
C | 1 | 1

db<>fiddle 此处

Answer 2

暂时忽略表结构...尝试使用 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  

结果：

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

话虽如此，你应该标准化模型。您可以通过将所有内容存储在一个带有日期（或月+年列）的表中，而不是为每个月使用单独的表，从而大大简化事情。

此外，您似乎正在存储有关特定产品随时间发生的事件的信息。如果是这种情况，您应该有一个包含唯一产品的单独表：

ProductId	ProductName
1	Product A
2	Product B
3	Product C

存储有关产品信息的其他表应存储“Product”表的唯一 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) 
;

结果：

product_name | sales_year | jan_sales | feb_sales | mar_sales
:----------- | ---------: | --------: | --------: | --------:
Product A    |       2022 |         0 |         2 |         0
Product B    |       2022 |         0 |         1 |         0
Product C    |       2022 |         0 |         1 |         0

Answer 3

--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