SELECT DISTINCT 最多返回一行

Question

给定以下数据库结构：

Regions

id	name
1	EU
2	US
3	SEA

Customers:

id	name	region
1	peter	1
2	henry	1
3	john	2

还有一个 PL/pgSQL函数到位，定义为 sendShipment()，它接受（除其他外）发送者和接收者客户 ID。

这存在一个业务限制，要求我们验证发送方和接收方是否位于同一区域 - 并且我们需要将其作为 sendShipment() 的一部分来执行。因此，在此函数中，我们需要查询客户表中的发送者和接收者 ID，并验证它们的区域 ID 是否相同。我们还需要识别自身以便进一步处理。

所以也许是这样的： SELECT DISTINCT Region FROM Customers WHERE id IN (?, ?)

这样做的问题是，结果要么是一个数组（如果客户不在同一区域内），要么是单个值。

有没有更优雅的方法来解决这个约束？我正在考虑SELECT INTO并使用临时表，或者我可以SELECT COUNT(DISTINCTregion)然后为实际的数据执行另一个SELECT如果计数小于 2，则为值，但我希望尽可能避免性能受到影响。

Answer 1

还有一个 PL/pgSQL 函数，定义为 sendShipment()，它接受（除其他外）发送者和接收者客户 ID。

这方面有一个业务限制，要求我们验证发送方和接收方是否位于同一区域 - 并且我们需要将其作为 sendShipment() 的一部分来执行。因此，在此函数中，我们需要查询客户表中的发送者和接收者 ID，并验证它们的区域 ID 是否相同。我们还需要识别自己的身份以便进一步处理。

此查询应该有效：

WITH q AS (
    SELECT
        COUNT( * ) AS CountCustomers,
        COUNT( DISTINCT c.Region ) AS CountDistinctRegions,
--      MIN( c.Region ) AS MinRegion
        FIRST_VALUE( c.Region ) OVER ( ORDER BY c.Region ) AS MinRegion
    FROM
        Customers AS c
    WHERE
        c.CustomerId = $senderCustomerId
        OR
        c.CustomerId = $receiverCustomerId
)
SELECT
    CASE WHEN q.CountCustomers = 2 AND q.CountDistinctRegions = 2 THEN 'OK' ELSE 'BAD' END AS "Status",
    CASE WHEN q.CountDistinctRegions = 2 THEN q.MinRegion END AS SingleRegion
FROM
    q

• 上述查询将始终返回包含 2 列的单行：Status 和 SingleRegion。

• SQL 没有“SINGLE( col )”聚合函数（即函数为 NULL，除非聚合组只有一行） ，但我们可以在 CTE 或派生表中将 MIN （或 MAX）与 CASE WHEN COUNT() 滥用作为等效操作< /del>.

  • 或者，可以使用窗口函数，但令人烦恼的是它们不适用于GROUP BY查询尽管非常相似，呃。
    • 再次强调，这是 ISO SQL 委员会的错误，而不是 PostgreSQL 的错误。

• 由于您的 Region 列是 UUID，因此您不能将其与 MIN 一起使用，但我知道它应该与 FIRST_VALUE( c.Region ) OVER ( ORDER BY c.Region ) AS MinRegion.

• 对于列：

  • 根据您提到的业务限制，Status 列可以是 'OK' 或 'BAD'。不过，您可能希望将其更改为位列，而不是文本列。
  • 如果 CountDistinctRegions = 2，无论 CountCustomers 为何，SingleRegion 列将为 NOT NULL（具有有效区域） >，但请随意更改，以防万一您仍然需要该信息。

Answer 2

对于对简单解决方案感兴趣的其他人，我最终想出了一种（明显的）方法：

SELECT
    r.region
FROM
    customers s
INNER JOIN customers r ON 
    s.region = r.region 
WHERE s.id = 'sender_id' and r.id = 'receiver_id';

SELECT DISTINCT 最多返回一行 他在这方面帮了我很多忙，而且发布了可行的解决方案。