奇怪的MySQL JOIN，我想包括匹配和不匹配的连接

Question

我需要获得查询的特定结果…我认为解决方案是同一个表的自连接…但是它没有像我期望的那样工作。

一点背景:时间框架;列表示时间增量数据集。"15 m"是一个15分钟数据点的数据集。"1 h"是1小时数据点的数据集。

表的内容:

priceID | price_time          | timeframe | price | thing
1       | 2020-01-01 10:00:00 | "15m"     | 150   | 0
2       | 2020-01-01 10:15:00 | "15m"     | 155   | 1
3       | 2020-01-01 10:30:00 | "15m"     | 140   | 0
4       | 2020-01-01 10:45:00 | "15m"     | 123   | 1
5       | 2020-01-01 11:00:00 | "15m"     | 159   | 0
6       | 2020-01-01 10:00:00 | "1h"      | 150   | 1
7       | 2020-01-01 11:00:00 | "1h"      | 159   | 0

这是我需要的记录集。我希望每个唯一的price_time对应一行，并为所有其他列创建一个唯一的列，按时间框架值分组。

price_time          | timeframe_15m | timeframe_1h | price_15m | price_1h | thing_15m | thing_1h
2020-01-01 10:00:00 | "15m"         | "1h"         | 150       | 150      | 0         | 1
2020-01-01 10:15:00 | "15m"         | NULL         | 155       | NULL     | 1         | NULL
2020-01-01 10:30:00 | "15m"         | NULL         | 140       | NULL     | 0         | NULL
2020-01-01 10:45:00 | "15m"         | NULL         | 123       | NULL     | 1         | NULL
2020-01-01 11:00:00 | "15m"         | "1h"         | 159       | 150      | 0         | 0

这是我认为可行的方法，但是行不通。

SELECT  
c.price_time
,c.timeframe AS price_time_15m
,c.price AS price_15m
,c.thing AS thing_15m
,o.timeframe AS timeframe_1h
,o.price AS price_1h
,o.thing AS thing_1h

FROM    tbl_prices c LEFT OUTER JOIN tbl_prices o ON c.price_time = o.price_time
WHERE   c.timeframe = '15m'
AND    o.timeframe = '1h'

我花了很长时间在这上面，我正式卡住了!任何帮助将非常感激!

Answer 1

使用条件聚合:

SELECT price_time,
MAX(CASE WHEN timeframe = '"15m"' THEN timeframe END) timeframe_15m,
MAX(CASE WHEN timeframe = '"1h"' THEN timeframe END) timeframe_1h,
MAX(CASE WHEN timeframe = '"15m"' THEN price END) price_15m,
MAX(CASE WHEN timeframe = '"1h"' THEN price END) price_1h,
MAX(CASE WHEN timeframe = '"15m"' THEN thing END) thing_15m,
MAX(CASE WHEN timeframe = '"1h"' THEN thing END) thing_1h       
FROM tbl_prices
GROUP BY price_time;