MySQL 加入三个表，如果为空则显示 0 [英] MySQL Join three tables and display 0 if null

问题描述

我有三张桌子:

<块引用>

person_table

身份证|姓名 |性别

1 |乔|男

2 |简|女

3 |珍妮特 |女

4|杰 |男性等等...

---

<块引用>

product_table

身份证|姓名

1 |杂志

2 |书

3 |纸

4 |小说

等等...

---

<块引用>

**person_product

person_id|product_id |数量

1 |1 |1

1 |3 |3

2 |3 |1

4 |4 |2

等等...

---

我尝试进行查询，该查询将返回如下表:person_id|人名 |产品名称|数量但我无法做到，如果让我们说约翰没有书，它应该显示(johns id) 约翰|书|0而不是跳过这一行.我哪里做错了?这是我设法想出的:

SELECT p.*, f.name, l.quantityFROM person_product AS lINNER JOIN people_table AS p ON l.person_id=p.idINNER JOIN product_table AS f ON l.product_id=f.id按 id 排序`

解决方案

您似乎正在生成所有人的报告，针对所有产品与相关数量;在大型数据集上，这可能需要一段时间，因为除了数量之外，您并没有专门将产品与人联系起来:

SELECTp.id,p.name,p.性别，f.名称，IFNULL(l.quantity,0) AS 数量FROM person_table AS pJOIN product_table AS fLEFT JOIN person_product AS lON l.person_id = p.idAND l.product_id = f.id按 p.id, f.name 排序

结果:

这或多或少是你所追求的吗?

I have three tables:

person_table

id| name | gender

1 | Joe | male

2 | Jane |female

3 | Janet | female

4| Jay | male etc...

---

product_table

id| name

1 | magazine

2 | book

3 |paper

4 | novel

etc...

---

**person_product

person_id| product_id | quantity

1 | 1 | 1

1 | 3 | 3

2 | 3 | 1

4 | 4 | 2

etc...

---

I have tried to make a query that will return a table like this: person_id| person_name | product_name| quantity but i can't make it so that if lets say John has no books, it should display (johns id) John|book|0 instead of just skipping this line. Where did i go wrong? here is what i managed to come up with:

SELECT p.*, f.name, l.quantity 
FROM person_product AS l 
INNER JOIN people_table AS p ON l.person_id=p.id 
INNER JOIN product_table AS f ON l.product_id=f.id
ORDER BY id`

解决方案

It seems that you're generating a report of all people, against all products with the relevant quantity; on a large data set this could take a while as you're not specifically joining product to person for anything other than quantity:

SELECT
p.id,
p.name,
p.gender,
f.name,
IFNULL(l.quantity,0) AS quantity

FROM person_table AS p

JOIN product_table AS f

LEFT JOIN person_product AS l
    ON l.person_id = p.id
    AND l.product_id = f.id

ORDER BY p.id, f.name

Which results in:

Is that more-or-less what you're after?

这篇关于MySQL 加入三个表，如果为空则显示 0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！