mysql - 在 SQL 中排序时如何保留逻辑分组？

Question

我有两个表，它们在父子关系中相关

Sale
sale_id | customer_name

SaleItem
item_id | sale_id | item_type

每条 Sale 记录都有一个或多个对应的 SaleItem 记录。我的目标是创建一个 Sales 列表，并将其相应的 SaleItems 显示为子项。我可以用这个查询来做到这一点:

SELECT s.sale_id, s.customer_name, si.item_id, si.item_type 
FROM Sale s, SaleItem si
WHERE s.sale_id=si.sale_id
ORDER BY s.sale_id, si.item_id

这会产生类似这样的结果(为清楚起见添加了空白行):

sale_id | customer_name | item_id | item_type
1       | Sam           | 1       | D
1       | Sam           | 2       | B
1       | Sam           | 3       | D

2       | Nate          | 4       | A
2       | Nate          | 5       | B

3       | Mike          | 6       | B
3       | Mike          | 7       | C

4       | Nate          | 8       | C
4       | Nate          | 9       | D

ORDER BY 子句导致 Sales 及其订购的 SaleItems 的逻辑分组。这为我提供了编写一个漂亮的可折叠 UI 来表示数据所需的数据:

-Sale 1 - Sam
 -B 
 -B 
 -D 
+Sale 2 - Nate
+Sale 3 - Mike
-Sale 4 - Nate
 -C
 -D

一切都很好，但是当我想按 item_type 对这个列表进行排序时会发生什么？我想以这样的结果集结束(为清楚起见，添加了空白行):

sale_id | customer_name | item_id | item_type
2       | Nate          | 4       | A
2       | Nate          | 5       | B

3       | Mike          | 6       | B
3       | Mike          | 7       | C

4       | Nate          | 8       | C
4       | Nate          | 9       | D

1       | Sam           | 1       | D
1       | Sam           | 2       | B
1       | Sam           | 3       | D

请注意，每个 Sale 及其相应的 SaleItems 保持在一起。基本上，我在这里需要做的是将每组 Sale+SaleItems 视为一个元素，并按任何列对它们进行智能排序。由于我们实际上只是在此处对 Sales 进行排序，而 item_type 适用于 SaleItem，因此我们只需按第一个 SaleItem 的 item_type 进行排序，并保持 Sale 中 SaleItems 的原始排序顺序。我真的希望这是有道理的。

我有几个问题:

1. 我是不是用错了方法？由于每条 Sale 记录都是一个或多个 SaleItem 记录的“父项”，因此 JOIN 似乎有点矫枉过正，因为我最终在每个“子”行中复制了“父项”数据。
2. 如何按每个销售的第一个 item_type 对这些数据进行排序，同时仍将每个销售的 SaleItems 放在一起？

非常感谢。

Answer 1

尝试这样的事情:

SELECT s.sale_id, 
       s.customer_name, 
       si.item_id, 
       si.item_type,
       min(si.item_type) OVER (PARTITION BY sale_id) as min_item_type
FROM Sale s, SaleItem si
WHERE s.sale_id=si.sale_id
ORDER BY min_item_type, s.sale_id, si.item_id

它会给你的结果接近你作为例子展示的结果。

您可以将 min(si.item_type) 替换为任何窗口函数(例如，max、first、last ...) 获取 item_type，它将“描述”每个 sale_id 组。