简单的sql:如何将其分为不同的列?

问题描述

假设我将股票价格保持在这样的3列表格中:

Say I keep stocks prices in a 3 column table like this:

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

我真的需要选择两次，为每个股票选择一次，然后外部加入结果吗?

Do I really need to select twice, once for each ticker, and then outer join the results?

推荐答案

像这样:

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

这篇关于简单的sql:如何将其分为不同的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！