查询未返回所有行

Question

设置：

create table product_stock(product_id integer, qty integer, branch_id integer); 

create table product(product_id integer, product_name varchar(255)); 

insert into product(product_id, product_name) 
     values(1, 'Apsana White DX Pencil'); 

insert into product(product_id, product_name) 
     values(2, 'Diamond Glass Marking Pencil'); 

insert into product(product_id, product_name) 
     values(3, 'Apsana Black Pencil'); 

insert into product_stock(product_id, qty, branch_id) 
     values(1, 100, 1); 

insert into product_stock(product_id, qty, branch_id) 
     values(1, 50, 2); 

insert into product_stock(product_id, qty, branch_id) 
     values(2, 80, 1); 

我的查询：

SELECT IFNULL(SUM(s.qty),0) AS stock, 
     product_name 
FROM product_stock s 
RIGHT JOIN product p ON s.product_id=p.product_id 
WHERE branch_id=1 
GROUP BY product_name 
ORDER BY product_name; 

回报：

+-------+-------------------------------+ 
| stock | product_name     | 
+-------+-------------------------------+ 
| 100 | Apsana White DX Pencil  | 
| 80 | Diamond Glass Marking Pencil | 
+-------+-------------------------------+ 
1 row in set (0.00 sec) 

但我想有以下结果：

+-------+------------------------------+ 
| stock | product_name     | 
+-------+------------------------------+ 
| 0 | Apsana Black Pencil   | 
| 100 | Apsana White DX Pencil  | 
| 80 | Diamond Glass Marking Pencil | 
+-------+------------------------------+ 

为了得到这个结果我应该运行哪些mysql查询？

Answer 1

尝试：

SELECT IFNULL(SUM(s.qty),0) AS stock, 
      product_name 
    FROM product_stock s 
    RIGHT JOIN product p ON s.product_id=p.product_id AND branch_id=1 
    GROUP BY product_name 
    ORDER BY product_name; 

您当前的查询筛选出来的产品不匹配product_stock在WHERE子句中，有效地将外连接返回到内连接。