sql - 按别名选择特定列，然后按别名排序

Question

我的表格如下

+----+----------+--------+
| id | priority |  User  |
+----+----------+--------+
|  1 |        2 | [null] |
|  2 |        1 | [null] |
|  3 |        3 | Tony   |
|  4 |        2 | John   |
|  5 |        2 | Andy   |
|  6 |        1 | Mike   |
+----+----------+--------+

我的目标是提取它们，并按以下组合条件排序:

1. 优先级 = 1
2. 用户为空

+----+----------+--------+-----------+
| id | priority |  User  | peak_rows |
+----+----------+--------+-----------+
|  1 |        2 | [null] | 1         |
|  2 |        1 | [null] | 1         |
|  6 |        1 | Mike   | 0         |
|  3 |        3 | Tony   | 1         |
|  4 |        2 | John   | 0         |
|  5 |        2 | Andy   | 0         |
+----+----------+--------+-----------+

这就是我想我能做的

select
    id,
    CASE WHEN priority = 1 THEN 1 ELSE 0 END as c1,
    CASE WHEN User is NULL THEN 1 ELSE 0 END as c2,
    c1 + c2 AS peak_rows
FROM mytable
ORDER BY peak_rows DESC

但它会导致错误:

ERROR:  column "c1" does not exist
LINE 5:  c1+ c2as pp
         ^
SQL state: 42703
Character: 129

我不知道为什么我做了2列(c1和c2)，但我以后不能用。

这样做有什么好主意吗？

Answer 1

您不是在制作两列并稍后使用它们，而是在制作它们并希望同时使用它们。您可以使用子查询。

SELECT a.id, a.priority, a.User, a.c1 + a.c2 AS peak_rows
FROM
(SELECT id,
        priority,
        User,
        CASE WHEN priority = 1 THEN 1 ELSE 0 END as c1,
        CASE WHEN User IS NULL THEN 1 ELSE 0 END as c2,
 FROM mytable) a
ORDER BY peak_rows DESC;