sql - 简单的sql : how do I group into separate columns?

Question

假设我将股票价格保存在一个包含 3 列的表格中，如下所示:

create table stocks(
    ticker text,
    day int,
    price int
);

insert into stocks values ('aapl', 1, 100);
insert into stocks values ('aapl', 2, 104);
insert into stocks values ('aapl', 3, 98);
insert into stocks values ('aapl', 4, 99);

insert into stocks values ('goog', 1, 401);
insert into stocks values ('goog', 2, 390);
insert into stocks values ('goog', 3, 234);

我想要这样的结果:

day aapl goog
1   100  401
2   104  390
3   98   234
4   99   null

我真的需要选择两次，每个代码一次，然后外连接结果吗？

Answer 1

像这样:

Select day,
   MAX(case WHEN ticker = 'aapl' then price end) as 'aapl',
   MAX(case WHEN ticker = 'goog' then price end) as 'goog'
From stocks
group by day

DEMO