表修改

Question

如果我有这样一张桌子：

name attribute
john hat
john glasses
john jacket
Lisa hat
Lisa boots

我可以使用什么查询来获得：

name attribute1 attribute2 attribute3
John hat         glasses   jacket
Lisa hat         boots     NULL

Answer 1

create local temporary table tmp_nr(NName varchar(50), maxnumber int) on commit preserve rows;
insert into tmp_nr
select Nname, count(*) as number
from T
group by Nname;

临时表，用于存储属性的数量。

select Nname,attributte,attributte2,attributte3 from
(
select Nname,
attributte,
lag(attributte,1) over(partition by Nname order by Nname) as attributte2,
lag(attributte,2) over(partition by Nname order by Nname) as attributte3,
row_number() over(partition by Nname order by Nname) as number
from T
)x inner join tmp_nr on x.Nname=tmp_nr.Nname
where x.number = tmp_nr.maxnumber

联接的目的是只选择每个人包含所有项的行。

Answer 2

ALTER table_name 添加 column_name数据类型

按此语法按此约定添加尽可能多的列

例如：

变更表供应商添加supplier_name VARCHAR(50)；