代码之家 › 专栏 › 技术社区 › Tareq

查询未返回所有行

mysql sql

-1

Tareq · 技术社区 · 17 年前

设置:

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查询?

1 回复 | 直到 16 年前

Tony Andrews 17 年前

尝试:

   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;

您当前的查询正在筛选WHERE子句中没有匹配产品库存的产品,从而有效地将外部联接转换回内部联接。

推荐文章