sql - 对于 CASE WHEN,SQL 不为空
问题描述
我当前的数据库采用以下格式,
| question_id |Tag | Question |
| - |- --|-- -|
|1 | EN | DOG |
|1 | DE | HUND |
我正在尝试创建一个结果,例如;
| question_id |English | German |
| - |- --|-- -|
|1 | DOG | HUND |
我目前在设置时有一个案例;
select question_id
, CASE WHEN tag = 'en'
THEN text
END AS "ENGLISH",
CASE WHEN tag = 'de'
THEN text
END AS "GERMAN"
from table
问题是因为我有两种情况,它最终返回重复的行
| question_id |English | German |
| - |- --|-- -|
|1 | NULL | HUND |
|1 | DOG | NULL |
我试图解决这个问题,是在语句的末尾添加一个 WHERE "ENGLISH" IS NOT NULL 来删除这些行,但是当列是真正的列时,SQL 似乎无法识别这种情况。
解决方案
您可以使用聚合:
SELECT question_id,
max(text) FILTER (WHERE tag = 'en') AS english,
max(text) FILTER (WHERE tag = 'de') AS german
FROM atable
GROUP BY question_id;