如何将列分割成按日期排序的多个列 [英] How to split a column into multiple columns ordered by date
问题描述
当我尝试以正确的格式获取数据库的数据时,我遇到了很多问题。
我正在寻找以几天的方式打印数据库输出的方式。
I'm having a lot of problems when trying to get the data of my database in a proper format. I'm looking for the way to print the output of my database in columns by days.
我的数据库有两列,一列有一个 timestamp
,格式如 YYYY-MM-DD H:M
,另一个与速度相关的数据的列。
My database has two columns, one with a timestamp
in a format like YYYY-MM-DD H:M
and another colum with data related to a speed.
我需要按日分列数据列。
I need to split the speed data in columns by day.
或多或少,我的数据库是这样的(with 288数据每天):
More or less, my database is something like this (with 288 data everyday):
Timestamp | Speed
2014-05-01 00:03:00 | 328.71
2014-05-01 00:18:00 | 331.31
2014-05-01 00:33:00 | 193.83
2014-05-02 00:03:00 | 321.21
2014-05-02 00:18:00 | 290.39
2014-05-02 00:33:00 | 283.43
我要找的是:
Hour | 2014-05-01 | 2014-05-02
00:03:00 | 328.71 | 321.21
00:18:00 | 331.31 | 290.39
00:33:00 | 193.83 | 283.43
首先,我试图以静态方式做,但是当我得到它我会做动态的。
First of all, I've tried to do in a static way, but when I get it, I'll do dynamic.
我所做的查询是
SELECT
(CASE WHEN Timestamp>='2014-05-01' AND Timestamp<='2014-05-03'
THEN date_format(Timestamp,'%H:%i') ELSE 0 END) AS 'Hour',
(CASE WHEN Timestamp>='2014-05-01' AND Timestamp<='2014-05-02'
THEN speed ELSE 0 END) AS '2014-05-01',
(CASE WHEN Timestamp>='2014-05-02' AND Timestamp<='2014-05-03'
THEN velocidad ELSE 0 END) AS '2014-05-02'
FROM `db_speed`
WHERE Timestamp>='2014-05-01' AND Timestamp<='2014-05-10
有了这个查询,我得到一个这样的结构:
With this query, I get a structure like this:
Hour | 2014-05-01 | 2014-05-02
00:03:00 | 328.71 | 0
00:18:00 | 331.31 | 0
00:33:00 | 193.83 | 0
00:03:00 | 0 | 321.21
00:18:00 | 0 | 290.39
00:33:00 | 0 | 283.43
推荐答案
你相当接近,你只是错过 group by
和聚合函数。
You are quite close, you're just missing group by
and aggregate function.
SELECT
(CASE WHEN Timestamp>='2014-05-01' AND Timestamp<='2014-05-03'
THEN date_format(Timestamp,'%H:%i') ELSE 0 END) AS 'Hour',
MAX((CASE WHEN Timestamp>='2014-05-01' AND Timestamp<='2014-05-02'
THEN speed ELSE NULL END)) AS '2014-05-01',
MAX((CASE WHEN Timestamp>='2014-05-02' AND Timestamp<='2014-05-03'
THEN speed ELSE NULL END)) AS '2014-05-02'
FROM `db_speed`
WHERE Timestamp>='2014-05-01' AND Timestamp<='2014-05-10'
GROUP BY `Hour`
- 看到它在一个 sqlfiddle
- see it working live in an sqlfiddle
这篇关于如何将列分割成按日期排序的多个列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!