mysql group查询,mysql使用GROUP BY查询结果

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