mysql使用GROUP BY查询结果 [英] mysql pivot query results with GROUP BY
问题描述
我有一个要导出为CSV的数据表。理想情况下，我想切换行和列，使数据分组更好一些。
为了进一步解释，目前数据库看起来像这样..
data_id data_timestamp data_value
----------------- ---------------------------
1 2011-07-07 00:01:00 0.400
1 2011- 07-07 00:02:00 0.500
1 2011-07-07 00:03:00 0.600
1 2011-07-07 00:04:00 0.700
2 2011-07- 07 00:01:00 0.100
2 2011-07-07 00:02:00 0.200
2 2011-07-07 00:03:00 0.250
2 2011-07-07 00 ：04：00 2.300
我想通过data_timestamp值对data_value进行分组，时间戳被分组，每个data_id的每个data_value显示在一列而不是一行中。
data_timestamp input_1 input_2
--------------------------------------------
2011-07-07 00:01:00 0.400 0.100
2011-07-07 00:02:00 0.500 0.200
2011-07-07 00:03:00 0.600 0.250
2011-07-07 00:04:00 0.700 2.300
下面是我使用的查询。 ..
SELECT d.data_timestamp，d.input_1，d.input_2
FROM（
SELECT data_timestamp ，
IF（data_id = 1，data_value，NULL）AS'input_1'，
IF（data_id = 2，data_value，NULL）AS'input_2'FROM data
）AS d ORDER BY data_timestamp ASC
但它不是我想要的，因为每当一个data_id没有值。
有没有建议？
EDIT：
我已经尝试过在外部查询中使用WHERE d.input_1 IS NOT NULL，
data_timestamp input_1 input_2
--------------------------------------------
2011-07-07 00:01:00 0.400 NULL
2011-07-07 00:01:00 NULL 0.100
2011-07-07 00:02:00 0.500 NULL
2011-07-07 00:02:00 NULL 0.200
2011-07-07 00:03:00 0.600 NULL
2011-07-07 00:03:00 NULL 0.250
2011 -07-07 00:04:00 0.700 NULL
2011-07-07 00:04:00 NULL 2.300
添加WHERE d.input_1 IS NOT NULL将丢弃input_2值。
data_timestamp input_1 input_2
--------------------------------------------
2011-07-07 00:01:00 0.400 NULL
2011-07-07 00:02:00 0.500 NULL
2011-07-07 00:03:00 0.600 NULL
2011- 07-07 00:04:00 0.700 NULL
此外，在现实中，我有大约20个id
PIVOT ing既不容易（也不好）。我更喜欢使用 CASE ：
SELECT d.data_timestamp
，SUM（CASE WHEN data_id = 1 THEN data_value ELSE 0 END）AS'input_1'
，SUM（CASE WHEN data_id = 2 THEN data_value ELSE 0 END）AS'input_2'
...
，SUM（CASE WHEN data_id = 20 THEN data_value ELSE 0 END）AS'input_20'
FROM data
GROUP BY data_timestamp
ORDER BY data_timestamp ASC
但 IF 在MySQL中也有效：
SELECT d.data_timestamp
，SUM（IF（data_id = 1，data_value，0））AS'input_1'
，SUM = 2，data_value，0））AS'input_2'
...
，SUM（IF（data_id = 20，data_value，0））AS'input_20'
FROM data
GROUP BY data_timestamp
ORDER BY data_timestamp ASC
或者，您可以使用20级 JOIN ：
SELECT d.data_timestamp
，d01。 data_value AS'input_1'
，d02.data_value AS'input_2'
...
，d20.data_value AS'input_20'
FROM
（SELECT DISTINCT d。 data_timestamp
FROM data
）AS d
LEFT JOIN数据AS d01
ON d01.data_timestamp = d.data_timestamp
AND d01.data_id = 1
LEFT JOIN数据AS d02
ON d02.data_timestamp = d.data_timestamp
AND d02.data_id = 2
... --- 20 JOINs
LEFT JOIN数据AS d20
ON d20.data_timestamp = d.data_timestamp
AND d20.data_id = 20
ORDER BY d.data_timestamp ASC
I have a table to data that I want to export to a CSV. Ideally, I'd like to switch the rows and columns around, so that the data is grouped a little better.
To explain further, currently, the database looks like this..
data_id data_timestamp data_value
--------------------------------------------
1 2011-07-07 00:01:00 0.400
1 2011-07-07 00:02:00 0.500
1 2011-07-07 00:03:00 0.600
1 2011-07-07 00:04:00 0.700
2 2011-07-07 00:01:00 0.100
2 2011-07-07 00:02:00 0.200
2 2011-07-07 00:03:00 0.250
2 2011-07-07 00:04:00 2.300
What I'd like to do group the data_value by the data_timestamp value, so that the timestamps are grouped, and each data_value for each data_id is shown in a column, instead of a row.
data_timestamp input_1 input_2
--------------------------------------------
2011-07-07 00:01:00 0.400 0.100
2011-07-07 00:02:00 0.500 0.200
2011-07-07 00:03:00 0.600 0.250
2011-07-07 00:04:00 0.700 2.300
Below is the query i'm using...
SELECT d.data_timestamp, d.input_1, d.input_2
FROM (
SELECT data_timestamp,
IF(data_id=1,data_value,NULL) AS 'input_1',
IF(data_id=2,data_value,NULL) AS 'input_2' FROM data
) AS d ORDER BY data_timestamp ASC
But it's not quite what i'm wanting, as there are now NULL values whenever one data_id doesn't have a value. GROUP BY seems to group the data_value's as well, which isn't what I want.
Any suggestions?
EDIT:
I've already tried using WHERE d.input_1 IS NOT NULL in the outer query, but can't quite get the results..
Before the WHERE...
data_timestamp input_1 input_2
--------------------------------------------
2011-07-07 00:01:00 0.400 NULL
2011-07-07 00:01:00 NULL 0.100
2011-07-07 00:02:00 0.500 NULL
2011-07-07 00:02:00 NULL 0.200
2011-07-07 00:03:00 0.600 NULL
2011-07-07 00:03:00 NULL 0.250
2011-07-07 00:04:00 0.700 NULL
2011-07-07 00:04:00 NULL 2.300
Adding WHERE d.input_1 IS NOT NULL will drop the input_2 values..
data_timestamp input_1 input_2
--------------------------------------------
2011-07-07 00:01:00 0.400 NULL
2011-07-07 00:02:00 0.500 NULL
2011-07-07 00:03:00 0.600 NULL
2011-07-07 00:04:00 0.700 NULL
Also, in reality, I have about 20 id's to group by, so wouldn't be the best of ideas to OR all of them either..
PIVOTing is neither easy (nor nice) to do. I prefer using CASE:
SELECT d.data_timestamp
, SUM( CASE WHEN data_id = 1 THEN data_value ELSE 0 END ) AS 'input_1'
, SUM( CASE WHEN data_id = 2 THEN data_value ELSE 0 END ) AS 'input_2'
...
, SUM( CASE WHEN data_id = 20 THEN data_value ELSE 0 END ) AS 'input_20'
FROM data
GROUP BY data_timestamp
ORDER BY data_timestamp ASC
but IF works in MySQL as well:
SELECT d.data_timestamp
, SUM( IF(data_id = 1, data_value, 0) ) AS 'input_1'
, SUM( IF(data_id = 2, data_value, 0) ) AS 'input_2'
...
, SUM( IF(data_id = 20, data_value, 0) ) AS 'input_20'
FROM data
GROUP BY data_timestamp
ORDER BY data_timestamp ASC
Alternatively, you can use a 20-level JOIN:
SELECT d.data_timestamp
, d01.data_value AS 'input_1'
, d02.data_value AS 'input_2'
...
, d20.data_value AS 'input_20'
FROM
( SELECT DISTINCT d.data_timestamp
FROM data
) AS d
LEFT JOIN data AS d01
ON d01.data_timestamp = d.data_timestamp
AND d01.data_id = 1
LEFT JOIN data AS d02
ON d02.data_timestamp = d.data_timestamp
AND d02.data_id = 2
... --- 20 JOINs
LEFT JOIN data AS d20
ON d20.data_timestamp = d.data_timestamp
AND d20.data_id = 20
ORDER BY d.data_timestamp ASC
这篇关于mysql使用GROUP BY查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
