SQL:在一个表中返回许多

Using a MySQL database I want to store daily measurements of different "channels":

使用MySQL数据库，我想存储不同“通道”的每日测量数据:

date | channel | value
-----+---------+------
1.1. |     'A' |     1
1.1. |     'B' |     2
1.1. |     'C' |     3
2.1. |     'A' |     4
2.1. |     'C' |     5
3.1. |     'A' |     6
3.1. |     'B' |     7
3.1. |     'C' |     8
4.1. |     'A' |     9
4.1. |     'B' |    10
4.1. |     'C' |    11

Each day the then available channels will be inserted (in the table above the channel 'B' wasn't available on the 2.1.). There will not be more than one measurement per channel per day, i.e. (date,channel) is always unique.

每天都要插入可用的通道(在表上的B通道在2.1中不可用)。每个通道每天不会有超过一个测量，也就是说(日期、通道)总是唯一的。

My question:
What is the SQL command to retrieve that data in one table, i.e. a result looking like:

我的问题是:在一个表中检索数据的SQL命令是什么?

date | A  | B  | C
-----+----+----+---
1.1. |  1 |  2 |  3
2.1. |  4 |  - |  5
3.1. |  6 |  7 |  8
4.1. |  9 | 10 | 11

Optional task: the channel are just a channel ID and a different table maps the ID to the channel name.

可选任务:通道只是一个通道ID，不同的表将ID映射到通道名。

(Actually I thought that this is so trivial that there are examples about it everywhere. But either my search engine is broken or I'm trying to do something strange here...)

(其实我觉得这个很琐碎，到处都有这样的例子。但要么我的搜索引擎坏了，要么我想做一些奇怪的事情……

Note: SQL Cross Tab Function doesn't solve this question as I need the result in separate columns and not aggregated into one.

注意:SQL Cross Tab函数不能解决这个问题，因为我需要将结果放在单独的列中，而不是聚合到一个列中。

1 个解决方案

#1

based on your example, you can try this approach

基于您的示例，您可以尝试这种方法

SELECT `date`,
       SUM(CASE WHEN channel_id = 'A' THEN `value` END) 'A',
       SUM(CASE WHEN channel_id = 'B' THEN `value` END) 'B',
       SUM(CASE WHEN channel_id = 'C' THEN `value` END) 'C'       
  FROM t
 GROUP BY `date`
 ORDER BY `date`

Result

结果

date    A   B       C
1.1.    1   2       3
2.1.    4   (null)  5
3.1.    6   7       8
4.1.    9   10      11