mysql - MySQL查询的ORDER BY和GROUP BY

Question

我有两个 mySQL 表如下:

[product] table
P_id | Name | Quantity
1    | B    | 10
2    | C    | 15
3    | A    | 8

[attribute] table 
P_id | Name  | Quantity
1    | Black | 5
1    | Red   | 5
2    | Blue  | 6
2    | Black | 9

如何编写一个 SQL 查询，以便它可以显示上述两个表的结果，如下所示:报告:

P_id | Name  | Quantity
3    | A     | 8
1    | B     | 10
1    | Black | 5
1    | Red   | 5
2    | C     | 15
2    | Black | 9
2    | Blue  | 6

这些应该在 [Name] 列上排序，但它们应该像上面那样在 P_id 列上分组。通过在 P_id 上“分组”，我的意思是“让所有具有相同 P_id 的记录彼此相邻”。是否可以使用单个 SQL 查询检索如上排列。

Answer 1

SELECT P_id, Name, Quantity FROM (
  SELECT P_id, Name, Quantity, Name as parent, 1 as level
    FROM product
  UNION
  SELECT a.P_id, a.Name, a.Quantity, p.Name as parent, 2 as level
    FROM attribute a JOIN product p ON a.P_id = p.P_id
) combined ORDER BY parent, level, Name