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

Question

表格:

| 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   |
+----+------+-------+------+

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 的一部分。