如何根据输出为查询结果添加类别? [英] How to add a category to the results of a query based on the output?

问题描述

将这个问题扩展到更一般的情况:

Extending this question for a more general case:

我有一个名为 fruits 的表，其中有一列 fruit 具有三个可能的值:apple、orange, 和 mango.我使用以下查询按 date 获取每个 fruit 的计数:

I have a table called fruits with a column fruit that has three possible values: apple, orange, and mango. I used the following query to get the counts of each fruit by date:

SELECT date, fruit, COUNT(id) AS count
FROM fruits
WHERE date BETWEEN '2020-10-02' AND '2020-10-05'
GROUP BY date, fruit

给出以下结果:

date           fruit      count
--------------------------------
2020-10-02     apple        3
2020-10-02     orange       5
2020-10-03     orange       23
2020-10-03     mango        1
2020-10-04     mango        9
2020-10-05     apple        10
2020-10-05     orange       7
2020-10-05     mango        6

如您所见，并非每个 date 都包含每种类型的 fruit.是否有可能，如果有，如何将每个 date 的 missing 水果添加到上述结果中，并使用默认的 count=0，如下图?

As you can see, not every date contains each type of fruit. Is it possible, and if so, how, to add the missing fruit by each date to the above results with a default count=0, as shown below?

date           fruit      count
--------------------------------
2020-10-02     apple        3
2020-10-02     orange       5
2020-10-02     mango        0
2020-10-03     orange       23
2020-10-03     mango        1
2020-10-03     apple        0
2020-10-04     mango        9
2020-10-04     apple        0
2020-10-04     orange       0
2020-10-05     apple        10
2020-10-05     orange       7
2020-10-05     mango        6

推荐答案

使用 cross join 来生成行，然后使用 left join 来引入结果:

Use a cross join ot generate the row and then a left join to bring in the results:

select d.date, f.fruit, coalesce(ff.count, 0) as count
from (select distinct date from fruits) d cross join
     (select distinct fruit from fruits) f left join
     fruits ff
     on ff.date = d.date and ff.fruit = f.fruit;

这篇关于如何根据输出为查询结果添加类别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！