mysql - 合并的用法

Question

我有一个查询，使用 COALESCE() 来计算 2 列的组合:

SELECT method, main_ingredient, COUNT(*) AS cnt FROM  `recipes` 
GROUP BY COALESCE( method, main_ingredient ) 

结果很有用。结果示例:

method    main_ingredient    cnt
================================
     1    4                  10
     2    1                  6
     3    6                  3
     4    6                  5
     5    2                  4
     6    8                  2

但是，如何获得COUNT(*)等于0的结果呢？

更新预期输出:

method    main_ingredient    cnt
================================
     1    2                  0
     1    3                  0
     1    5                  0
     1    6                  0
     2    2                  0
     2    3                  0
                .
                .
                .
                .
               etc

更新添加了tbl_methods和tbl_main_ingredients:

tbl_methods 的架构:

id    method_name
=================
1     Method 1
2     Method 2
      .
      .
      .
6     Method 6

tbl_main_ingredients 的架构:

id     ingredient_name
======================
1      Ingredient 1
2      Ingredient 2
       .
       .
       .
8      Ingredient 8

两个id都是各自表的主键，自增。

Answer 1

首先，您需要在tbl_methods和tbl_main_ingredients表之间进行CROSS JOIN，以获得方法和成分的所有可能组合。

稍后根据匹配的 method 和 main_ingredient 在上述交叉连接表和您的 reipes 表之间进行左连接。

因此，您将获得 method 和 main_ingredient 所有可能组合的结果。如果recipes表中存在任何组合，那么您将获得相应的计数，否则您将获得0作为计数。

SELECT 
method_ingredients.method_id,
method_ingredients.ingredients_id,
COUNT(R.method) AS cnt
FROM 
(
    SELECT 
    TM.id AS method_id,
    TMI.id AS ingredients_id
    FROM tbl_methods TM
    CROSS JOIN tbl_main_ingredients TMI
) AS method_ingredients
LEFT JOIN `recipes` R ON R.method = method_ingredients.method_id AND R.main_ingredient = method_ingredients.ingredients_id
GROUP BY method_ingredients.method_id, method_ingredients.ingredients_id
ORDER BY method_ingredients.method_id, method_ingredients.ingredients_id;

或者

您可以选择此查询的较短版本:

SELECT 
 TM.id AS method_id,
 TMI.id AS ingredients_id,
 COUNT(R.method) AS cnt
FROM tbl_methods TM
CROSS JOIN tbl_main_ingredients TMI
LEFT JOIN `recipes` R ON R.method = TM.id AND R.main_ingredient = TMI.id
GROUP BY TM.id, TMI.id
ORDER BY TM.id, TMI.id;

更多:

有关 COUNT 的一些微妙之处:

SELECT COUNT(0);   Result: 1

SELECT COUNT(-1);  Result: 1

SELECT COUNT(NULL); Result: 0

SELECT COUNT(71); Result: 1

SQL FIDDLE

顺便说一句在您的用例中与COALESCE无关。如果存在任何其他 NULL 元素，COALESCE 返回列表中的第一个 非 NULL 元素。

示例:

SELECT COALESCE(NULL,NULL,NULL,'abc',NULL,'def'); returns abc

SELECT COALESCE(NULL,NULL,NULL);  returns NULL