sql - 如何在postgresql中按顺序选择不同的？

Question

表x：

| ID |  SITE| TIME  | TYPE|
|----|------|-------|-----|
| aa | 100  | 12-18 | fo  |
| aa | 101  | 12-10 | ba  |
| bb | 102  | 12-10 | fo  |
| cc | 100  | 12-09 | ba  |
| cc | 109  | 12-01 | fo  |
| dd | 100  | 12-08 | fo  |

fo 等于 2，ba 等于 1。
我想按类型排序并将其插入另一个表。

INSERT INTO "NUtable"
SELECT DISTINCT ON("ID")
*
FROM
TABLEX
ORDER BY
"ID","SITE","TIME",(CASE "TYPE" WHEN 'fo' then 2
WHEN 'ba' then 1
ELSE NULL END
) DESC

SQL 似乎工作正常。但是，结果在某些行中出错了。为什么这样？

这是 cc 的示例得到了错误的结果： http ://sqlfiddle.com/#!17/0c0e4/2

结果应该是

+----+------+-------+------+
| ID | SITE | TIME  | TYPE |
+----+------+-------+------+
| aa |  100 | 12-18 | fo   |
| bb |  102 | 12-10 | fo   |
| cc |  109 | 12-01 | fo   |
| dd |  100 | 12-08 | fo   |
+----+------+-------+------+

代替

+----+------+-------+------+
| ID | SITE | TIME  | TYPE |
+----+------+-------+------+
| aa |  100 | 12-18 | fo   |
| bb |  102 | 12-10 | fo   |
| cc |  100 | 12-09 | ba   |
| dd |  100 | 12-08 | fo   |
+----+------+-------+------+

标签： sqlpostgresql

Answer 1

您需要按类型排序作为第二个键：

SELECT DISTINCT ON ("ID") x.*
FROM TABLEX x
ORDER BY "ID",
         (CASE "TYPE" WHEN 'fo' THEN 2 WHEN 'ba' then 1
               ELSE NULL
          END) DESC,
         "SITE", "TIME";

DISTINCT ON获取每个 遇到的第一行"ID"。您希望该行具有特定类型，因此该类型的逻辑应该是ORDER BY.