mysql - 如何为不存在的值显示零行

Question

如果标题不能代表问题，我很抱歉，因为我不是以英语为母语的人，而且我不知道如何命名这种情况。感谢标题推荐。

我有以下查询

SELECT mp.PerTPay,bz.BizCode,bz.BizName,count(*) PerCount FROM tb_perbiz bz
LEFT JOIN ms_per mp ON BizCode=mp.PerBiz
GROUP BY bz.BizCode,mp.PerTPay
ORDER BY bz.BizCode,mp.PerTPay

产生结果

PerTPay  BizCode   BizName  PerCount 
1        01        AAAAA    2
1        02        BBBBB    9
1        03        CCCCC    66
2        03        CCCCC    83
1        04        DDDDD    57
2        04        DDDDD    103

但下面是我想要的结果。我希望为每个 BizCode 显示所有可能的 PerTPay(1 和 2)。

PerTPay  BizCode   BizName  PerCount 
1        01        AAAAA    2
2        01        AAAAA    0
1        02        BBBBB    9
2        02        BBBBB    0
1        03        CCCCC    66
2        03        CCCCC    83
1        04        DDDDD    57
2        04        DDDDD    103

这些是我的表格

"ms_per"
PerCode    PerBiz    PerTPay
0001       01        1
0002       01        1
0003       02        1
0004       02        2
and so on

"tb_perbiz"
BizCode   BizName
01        AAAAA
02        BBBBB
03        CCCCC
04        DDDDD

我无法将查询的主表更改为 ms_per，因为我显示的查询是从一个非常大的查询减少而来的，它主要依赖于 tb_perbiz

Answer 1

通常，要在结果中获得特定组合，您需要先生成该组合。如果您将数据与包含 PerTPay 所有允许值的表连接起来，您就可以这样做 - 这实际上与您加入 tb_perbiz 的原因相同 -表:包含 BizCode 所有允许值的表。这确保您获得所有代码，即使它不存在于 ms_per 中。否则，您可以只对表 ms_per 进行分组。

因为您只有两个值，所以您可以动态生成该表:

SELECT pt.PerTPay, bz.BizCode, max(bz.BizName) as BizName, 
       count(mp.PerCode) PerCount 
FROM tb_perbiz bz
CROSS JOIN (select 1 as PerTPay union select 2) pt
LEFT JOIN ms_per mp ON bz.BizCode=mp.PerBiz and mp.PerTPay = pt.PerTPay
GROUP BY bz.BizCode,pt.PerTPay
ORDER BY bz.BizCode,pt.PerTPay;

cross join 将为您提供所有可能的组合，无论它们是否存在于 ms_per 中。

请注意，分组现在是在那个新“表”上完成的，并且我将 count(*) 更改为 count(mp.PerCode) 以确保只计算 ms_per 中的现有行。我还向 BizName 添加了一个聚合函数 (max(bz.BizName))，因为它不是 group by 的一部分(尽管我猜测 BizCode 无论如何都是您的主键)。

虽然我在这里使用子查询来动态生成表，但您可能想要添加一个实际的表(如果您还没有这样的表，例如在外键中使用它)。它将例如允许您在那里简单地添加一个 3，您的查询和所有类似的查询将产生预期的新结果，而无需更改任何查询代码。