即使结果不存在，MYSQL也会显示0 [英] MYSQL show 0 even if results do not exist

问题描述

我有一个包含2个表的MySql数据库:

I have a MySql database with 2 tables:

countries
results

• 国家是正义的，而id和country name.
• 结果是country_id，value和date.
• 并非每个国家/地区在每个日期都有结果.
• Countries is just and id and a country name.
• Results is a country_id, a value and a date.
• Not all countries have results on each date.

如何创建一个查询，列出所有国家及其特定结果，但仍然列出它们，如果结果表上没有结果，则显示0?

How can I create a query that lists all countries with their specific result, but still list them and shows 0 if there is no result on the results table?

推荐答案

在两个表之间使用LEFT JOIN

Use a LEFT JOIN between the two tables

select c.id, c.name, IFNULL(r.value, 0) value, r.date
from countries c
LEFT JOIN results r on r.country_id = c.id

如果没有结果，则显示0(对于value列)，请使用

To show 0 (for the value column) if there is no result, use IFNULL.

这篇关于即使结果不存在，MYSQL也会显示0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！