sql - 一行的多个条件如果不存在则返回 NULL postgresql

Question

我有这样的架构:

[ad_id] .  [name] . [valueofname]
 1 .        name .    "brian"
 1 .        age  .    "23"
 2 .        job  .    "IT"
 2 .        name .    "Jack" 

行名称包含多个值:年龄、姓名、生日、工作、年龄我想将其转换为:

[ad_id] .      [name]  .       [age] .              [birthday] .    [job]
[valueofad_id][valueofname] [valueofnameofage] [valueofnameofbirth] [valueofnameofjob]

我在下面做了这个查询选择来修复它，所以在我的程序中我必须得到结果 ad_id ='xxxx' 代表每个 when name =' name或 age或 birthday或 job '

一些 ad_id没有所有名称，正如您在架构下方看到的 ad_id= 1只有 name和 age但不是 job所以我想在找不到工作时返回 NULL

 [ad_id] .  [name] . [valueofname]
  1 .        name .    "brian"
  1 .        age  .    "23"
  2 .        job  .    "IT"
  2 .        name .    "Jack" 


select ad_id,

max(case when name = 'name' and ad_id='xxx' then valueofname end) as name,
max(case when name = 'age' and ad_id='xxx'  then valueofname end) as age,
max(case when name = 'birthday' and ad_id='xxx' then valueofname end) as birthday,
max(case when name = 'job' and ad_id='xxx' then valueofname end) as job

from t
group by ad_id;

Answer 1

要展平所有 ad_id 的记录，请使用:

select ad_id,
       max(case when name = 'name' then valueofname end) as name,
       max(case when name = 'age'then valueofname end) as age,
       max(case when name = 'birthday' then valueofname end) as birthday,
       max(case when name = 'job' then valueofname end) as job
from t
group by ad_id;

要获取单个 ad_id 的结果，请添加一个 where 子句:

select ad_id,
       max(case when name = 'name' then valueofname end) as name,
       max(case when name = 'age'then valueofname end) as age,
       max(case when name = 'birthday' then valueofname end) as birthday,
       max(case when name = 'job' then valueofname end) as job
from t
where ad_id = 'xxx' 
group by ad_id;

注意:如果没有包含 ad_id = 'xxx' 的行，则不会返回任何行。