如何在具有外部联接和分组依据的查询中包括NULL值 [英] How to include NULL values in a query with Outer Join and Group By
问题描述
我有两个带有以下示例数据的表:
I have two tables with the following sample data:
Table 1: `item_name`
| item_id | item_desc |
| 1 | apple |
| 2 | orange |
| 3 | banana |
| 4 | grape |
| 5 | mango |
Table 2: `user_items`
| user_id | item_id |
| 127 | 1 |
| 127 | 2 |
| 127 | 4 |
| 128 | 1 |
| 128 | 5 |
我正在尝试使用以下查询选择user_id 127和128拥有的每个item_id的总数,以及对应的item_desc:
I'm trying to select a total of each item_id both user_id 127 and 128 have, along with the corresponding item_desc using the following query:
SELECT IFNULL(COUNT(ui.user_id), 0) AS total, in.item_desc
FROM user_items AS ui
RIGHT OUTER JOIN item_name AS in
ON ui.item_id = in.item_id
WHERE ui.user_id IN (127, 128)
GROUP BY ui.item_id
ORDER BY total DESC
上述查询的结果是:
| total | item_desc |
| 2 | apple |
| 1 | orange |
| 1 | grape |
| 1 | mango |
但是它不包含item_id 3,banana,我想使用RIGHT OUTER JOIN来检索.我希望得到这样的结果:
but it didn't include item_id 3, banana, which I wanted to retrieve with RIGHT OUTER JOIN. I was hoping to get a result that looked like this:
| total | item_desc |
| 2 | apple |
| 1 | orange |
| 1 | grape |
| 1 | mango |
| 0 | banana |
是否有任何方法可以修改查询以得到上述预期结果? 谢谢您的时间.
Is there any way to modify the query to end up with the intended result above? Thank you for your time.
推荐答案
使用count的查询中存在一些错误.这行得通.
There was a little error in your query using count. This works.
select count(ui.item_id) as total, in.item_desc
from item_name `in`
left join user_items ui on ui.item_id = in.item_id
and ui.user_id in (127, 128)
group by
in.item_desc
order by total desc
这篇关于如何在具有外部联接和分组依据的查询中包括NULL值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!