查询返回的行太少 [英] Query returns too few rows
本文介绍了查询返回的行太少的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
设置:
mysql> create table product_stock(
product_id integer, qty integer);
Query OK, 0 rows affected (0.17 sec)
mysql> create table product(
product_id integer, product_name varchar(255));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into product(product_id, product_name)
values(1, 'Apsana White DX Pencil');
Query OK, 1 row affected (0.05 sec)
mysql> insert into product(product_id, product_name)
values(2, 'Diamond Glass Marking Pencil');
Query OK, 1 row affected (0.03 sec)
mysql> insert into product(product_id, product_name)
values(3, 'Apsana Black Pencil');
Query OK, 1 row affected (0.03 sec)
mysql> insert into product_stock(product_id, qty)
values(1, 100);
Query OK, 1 row affected (0.03 sec)
我的第一个查询:
mysql> SELECT IFNULL(SUM(s.qty),0) AS stock,
product_name
FROM product_stock s
INNER JOIN product p ON p.product_id=s.product_id
GROUP BY product_name
ORDER BY product_name;
返回:
+-------+---------------------------+
| stock | product_name |
+-------+---------------------------+
| 100 | Apsana White DX Pencil |
+-------+---------------------------+
1 row in set (0.00 sec)
但是我想得到以下结果:
But I want to have the following result:
+-------+------------------------------+
| stock | product_name |
+-------+------------------------------+
| 0 | Apsana Black Pencil |
| 100 | Apsana White DX Pencil |
| 0 | Diamond Glass Marking Pencil |
+-------+------------------------------+
要获得此结果,我应该运行什么mysql查询?
To get this result what mysql query should I run?
推荐答案
您需要翻转联接并使用LEFT JOIN代替INNER JOIN:
You need to flip your join around and use LEFT JOIN instead of INNER JOIN:
SELECT IFNULL(SUM(s.qty),0) AS stock, product_name
FROM product AS p
LEFT JOIN product_stock AS s ON p.product_id=s.product_id
GROUP BY product_name
ORDER BY product_name;
这篇关于查询返回的行太少的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文