SELECT DISTINCT + ORDER BY附加表达式

Question

我没有使用PostgreSQL的经验，我正在将Rails5+MySQL应用程序迁移到Rails5+PostgreSQL，我有一个查询问题。

我已经看了一些问题/答案，但仍然不能解决我的问题。我的问题似乎很荒谬，但我需要在这里寻求帮助!查询:

SELECT DISTINCT users.* FROM users 
INNER JOIN areas_users ON areas_users.user_id = users.id 
INNER JOIN areas ON areas.deleted_at IS NULL AND areas.id = areas_users.area_id 
WHERE users.deleted_at IS NULL AND users.company_id = 2 AND areas.id IN (2, 4, 5) 
ORDER BY CASE WHEN users.id=3 THEN 0 WHEN users.id=5 THEN 1 END, users.id, 1 ASC

在DBeaver中运行查询，返回错误:

SQL Error [42P10]: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

我需要做些什么才能把这个SELECT DISTINCT和这个ORDER BY CASE一起使用?

Answer 1

就像错误信息说:

for SELECT DISTINCT, ORDER BY expressions must appear in select list

是一个表达式:

CASE WHEN users.id=3 THEN 0 WHEN users.id=5 THEN 1 END

在做SELECT DISTINCT users.* FROM ...时不能按它排序，因为它只允许出现在SELECT列表中的ORDER BY表达式。

通常，DISTINCT的最佳解决方案是首先不要使用它。如果不复制行，就不必在以后删除重复行。看到:

• 如何加快选择不同?

在您的示例中，使用EXISTS半连接(表达式/子查询)而不是连接。这避免了重复。假设表users中有不同的行，则DISTINCT没有工作。

SELECT u.*
FROM   users u
WHERE  u.deleted_at IS NULL
AND    u.company_id = 2
AND    EXISTS (
SELECT FROM areas_users au JOIN areas a ON a.id = au.area_id
WHERE  au.user_id = u.id
AND    a.id IN (2, 4, 5)
AND    a.deleted_at IS NULL
)
ORDER BY CASE u.id WHEN 3 THEN 0
WHEN 5 THEN 1 END, u.id, 1;  -- ①

执行您的请求，而且通常也快得多。

使用simple ("switched")CASE语法。

仍然有一个丑陋的部分。在ORDER BY中使用位置引用可以提供方便的简短语法。但是当你有SELECT *时，这是一个非常糟糕的主意。如果基础表中列的顺序发生了变化，则查询将默默地发生变化。在这个用例中拼出列!

(通常，您首先不需要SELECT *，而只需要列的选择。)

如果你的ID列保证是正数，这将会快一点:

...
ORDER BY CASE u.id WHEN 3 THEN -2
WHEN 5 THEN -1
ELSE u.id END, <name_of_first_column>

我必须使用DISTINCT

(真的吗?)如果你坚持:

SELECT DISTINCT CASE u.id WHEN 3 THEN -2 WHEN 5 THEN -1 ELSE u.id END AS order_column, u.*
FROM   users u
JOIN   areas_users au ON au.user_id = u.id
JOIN   areas a ON a.id = au.area_id
WHERE  u.deleted_at IS NULL
AND    u.company_id = 2
AND    a.id IN (2, 4, 5)
AND    a.deleted_at IS NULL
ORDER  BY 1, <name_of_previously_first_column>;  -- now, "ORDER BY 1" is ok

您将在结果中获得额外的列order_column。您可以使用不同的SELECT…

将其封装在子查询中。只是一个概念的证明。不要用这个

或DISTINCT ON?

SELECT DISTINCT ON (CASE u.id WHEN 3 THEN -2 WHEN 5 THEN -1 ELSE u.id END, <name_of_first_column>)
u.*
FROM   users u
JOIN   areas_users au ON au.user_id = u.id
JOIN   areas a ON a.id = au.area_id
WHERE  u.deleted_at IS NULL
AND    u.company_id = 2
AND    a.id IN (2, 4, 5)
AND    a.deleted_at IS NULL
ORDER  BY CASE u.id WHEN 3 THEN -2 WHEN 5 THEN -1 ELSE u.id END, <name_of_first_column>;

不返回额外的列。仍然只是概念的证明。不要使用它，EXISTS查询要便宜得多。

:

• 选择每个GROUP BY组的第一行?