属性表ATTR里部分结构如下(此表有n条数据)
id | name |
---|---|
1 | 男 |
2 | 女 |
3 | 汉族 |
4 | 其他民族 |
11 | 外国人 |
… | … |
用户属性表USERATTR里部分结构如下
id | a1 | a2 | … | a30 |
---|---|---|---|---|
1 | 2 | 4 | … | 11 |
2 | null | 3 | … | null |
… | … | … | … | … |
现在需要检索用户的30个属性对应的名字
方法1:
- 把select case a1 when c1 then c2 end from tmp1 where a1 =c1) n1改成(case a1 when c1 then c2 end) n1,会报错 missing FROM-clause entry for table
- 把select case a1 when c1 then c2 end from tmp1 where a1 =c1) n1改成select case a1 when c1 then c2 end from tmp1) n1,会报错 more than one row returned by a subquery used as an expression,因为tmp1有n条数据,去掉where a1 =c1就会按笛卡尔积生成表
- tmp1的union 可以临时加表里没有的属性
with
tmp1 as (select id c1,name c2 from ATTR union select -1,'外星人'),
tmp2 as (select * from USERATTR)
select id,
(select case a1 when c1 then c2 end from tmp1 where a1 =c1) n1,
(select case a2 when c1 then c2 end from tmp1 where a2 =c1) n2,
...........
from tmp2 --order by id
下面的sql更简单更快。上面的sql的where a1 =c1改成where c1 =a1更容易理解
with
tmp1 as (select id c1,name c2 from ATTR union select -1,'外星人'),
tmp2 as (select * from USERATTR)
select id,
(select c2 from tmp1 where c1=a1) n1,
(select c2 from tmp1 where c1=a2) n2,
...........
from tmp2 --order by id
结果如下
id | n1 | n2 | … | n30 |
---|---|---|---|---|
1 | 女 | 其他民族 | … | 外国人 |
2 | null | 汉族 | … | null |
… | … | … | … | … |
方法2
with
tmp1 as (select id c1,name c2 from ATTR),
tmp2 as (select * from USERATTR)
select id,
case attribute_value1 when tmp1.c1 then tmp1.c2 end n1,
...........
from tmp1 inner join tmp2 on 1=1 order by id
结果如下,每个user有n条数据,数据量大时速度很慢
id | n1 | n2 | … | n30 |
---|---|---|---|---|
1 | 女 | null | … | null |
1 | null | 其他民族 | … | null |
1 | … | … | … | … |
1 | null | null | … | 外国人 |
2 | null | null | … | null |
2 | null | 汉族 | … | null |
2 | … | … | … | … |
… | … | … | … | … |