sql - 如果特定列上有重复值，我如何只能“选择”（不聚合）一行？

Question

现在我有这个查询：

SELECT DISTINCT
    stock_picking.id          as delivery_order_id,
    sale_order.id             as sale_order_id,
    sale_order.name           as sale_order_name,
    stock_picking.origin      as stock_picking_origin,
    stock_picking.name        as stock_picking_name,
    stock_picking.create_date as stock_picking_create_date,
    sub.count_origin          as sale_order_delivery_order_done_count
FROM
(
    SELECT
        origin,
        COUNT(origin) as count_origin
    FROM stock_picking
    WHERE state = 'done'
    GROUP BY origin
    HAVING COUNT(origin) > 1
    ORDER BY origin
) sub
JOIN sale_order      ON sale_order.name = sub.origin
JOIN account_invoice ON account_invoice.origin = sale_order.name
JOIN stock_picking   ON stock_picking.origin = sale_order.name
WHERE
    account_invoice.create_date >= '04/17/20'       AND
    sale_order.create_date      <= '04/01/20 07:00' AND
    sale_order.create_date      >= '03/01/20'
ORDER BY sale_order.name
;

它返回：

+-------------------+---------------+-----------------+----------------------+--------------------+----------------------------+--------------------------------------+
| delivery_order_id | sale_order_id | sale_order_name | stock_picking_origin | stock_picking_name | stock_picking_create_date  | sale_order_delivery_order_done_count |
+-------------------+---------------+-----------------+----------------------+--------------------+----------------------------+--------------------------------------+
|           2053131 |       5840046 |         3258428 |              3258428 | WH/OUT/1804215     | 2020-03-01 07:10:32.144694 |                                    2 |
|           2071149 |       5840046 |         3258428 |              3258428 | WH/OUT/1819605     | 2020-03-03 18:00:25.208632 |                                    2 |
|           2154480 |       5840046 |         3258428 |              3258428 | WH/OUT/1894584     | 2020-03-11 08:39:33.514114 |                                    2 |
|           2053494 |       5840408 |         3258728 |              3258728 | WH/OUT/1804574     | 2020-03-01 07:41:26.728154 |                                    2 |
|           2105133 |       5840408 |         3258728 |              3258728 | WH/OUT/1849288     | 2020-03-07 13:59:10.049683 |                                    2 |
|           2192492 |       5840408 |         3258728 |              3258728 | WH/OUT/1929553     | 2020-03-13 09:10:26.18469  |                                    2 |
|           2061022 |       5861189 |         3279458 |              3279458 | WH/OUT/1811084     | 2020-03-02 14:37:35.803326 |                                    2 |
|           2170656 |       5861189 |         3279458 |              3279458 | WH/OUT/1909477     | 2020-03-12 08:57:15.434752 |                                    2 |
|           2072002 |       5885577 |         3294059 |              3294059 | WH/OUT/109633      | 2020-03-04 02:44:03.302924 |                                    2 |
|           2130430 |       5885577 |         3294059 |              3294059 | WH/OUT/114259      | 2020-03-10 03:13:58.33838  |                                    2 |
+-------------------+---------------+-----------------+----------------------+--------------------+----------------------------+--------------------------------------+

我想确保该列sale_order_id是唯一的，但从最少而不是聚合中挑选。delivery_order_id

我想要这样的结果：

+-------------------+---------------+-----------------+----------------------+--------------------+----------------------------+--------------------------------------+
| delivery_order_id | sale_order_id | sale_order_name | stock_picking_origin | stock_picking_name | stock_picking_create_date  | sale_order_delivery_order_done_count |
+-------------------+---------------+-----------------+----------------------+--------------------+----------------------------+--------------------------------------+
|           2053131 |       5840046 |         3258428 |              3258428 | WH/OUT/1804215     | 2020-03-01 07:10:32.144694 |                                    2 |
|           2053494 |       5840408 |         3258728 |              3258728 | WH/OUT/1804574     | 2020-03-01 07:41:26.728154 |                                    2 |
|           2061022 |       5861189 |         3279458 |              3279458 | WH/OUT/1811084     | 2020-03-02 14:37:35.803326 |                                    2 |
|           2072002 |       5885577 |         3294059 |              3294059 | WH/OUT/109633      | 2020-03-04 02:44:03.302924 |                                    2 |
+-------------------+---------------+-----------------+----------------------+--------------------+----------------------------+--------------------------------------+

标签： sqlpostgresql

Answer 1

您可以使用distinct on. 您的查询很复杂，因此我将其封装在 CTE 中：

with q as (
      . . . 
     )
select distinct on (sale_order_id) q.*
from q
order by sale_order_id, delivery_order_id;