使用LEFT JOIN分类相同的行而不重复一对多的关系

Question

请原谅我的问题标题，我不知道该怎么说，我有这两个表如下。

products    orders 
+------+----------+  +--------+------+-------+ 
| id | name  |  | id | qty | pid | 
+------+----------+  +--------+------+-------+ 
| 1 | mouse |  | 10001 | 20 | 1 | 
| 2 | keyboard |  | 10002 | 15 | 3 | 
| 3 | headset |  | 10004 | 5 | 3 | 
+------+----------+  | 10005 | 12 | 2 | 
         | 10006 | 18 | 1 | 
         +--------+------+-------+ 

这是LEFT JOIN我使用的查询和输出

SELECT p.id AS No, p.name AS ProductName, o.qty AS Quantity 
FROM products AS p 
LEFT JOIN orders AS o ON p.id = o.pid 

+------+-------------+----------+ 
| No | ProductName | Quantity | 
+------+-------------+----------+ 
| 1 | mouse  | 20  | 
| 1 | mouse  | 18  | 
| 2 | keyboard | 12  | 
| 3 | headset  | 15  | 
| 3 | headset  | 5  | 
+------+-------------+----------+ 

我所试图实现的是如下的输出：

+------+-------------+----------+ 
| No | ProductName | Quantity | 
+------+-------------+----------+ 
| 1 | mouse  | 20  | 
|  |    | 18  | 
| 2 | keyboard | 12  | 
| 3 | headset  | 15  | 
|  |    | 5  | 
+------+-------------+----------+ 

我的问题是有可能这样做？任何回复和建议非常感谢。谢谢。 P/S：我也试过使用GROUP_CONCAT（qty SEPARATOR“，”），但它将结果返回一行，因为我可能会在将来的Orders表中添加更多的附加列，它将会难以阅读。

Answer 1

在这种情况下，你可以使用MySQL变量。我将以前的产品ID存储在变量@prev中，并且只有当它更改时，我们才输出产品名称。

http://www.sqlfiddle.com/#!2/d5fd6/9

SET @prev := NULL; 

SELECT 
    IF(@prev = p.id, NULL, p.id) AS No, 
    IF(@prev = p.id, NULL, p.name) AS ProductName, 
    o.qty AS Quantity 
,@prev := p.id 

FROM products AS p 

LEFT JOIN orders AS o 
     ON p.id = o.pid 

Answer 2

当然，这可能—和无需使用的变量：

SELECT IF(c.min_oid IS NOT NULL, a.id, NULL) AS No, 
      IF(c.min_oid IS NOT NULL, a.name, NULL) AS ProductName, 
      b.qty AS Quantity 
FROM  products a 
JOIN  orders b ON a.id = b.pid 
LEFT JOIN (
      SELECT MIN(id) AS min_oid 
      FROM  orders 
      GROUP BY pid 
     ) c ON b.id = c.min_oid 
ORDER BY a.id, 
      b.id 

基本上它做什么是如果该行不特定产品的最低订单ID，显示空（NULL），否则显示信息。

---

SQLFiddle Demo