MySQL 创建具有可变列名的 View

Question

我使用 1-Wire 温度传感器和名为“LogTemp”的 Windows 应用程序记录温度值。

此应用程序自动将温度值存储在 MySQL 数据库中。

这是温度记录数据库:

mysql> show columns from logtemp; 
+------------------+----------+------+-----+---------------------+-------+ 
| Field | Type | Null | Key | Default | Extra | 
+------------------+----------+------+-----+---------------------+-------+ 
| DATETIME | datetime | NO | PRI | 0000-00-00 00:00:00 | | 
| 0400080224D59710 | float | YES | | NULL | | 
| CA00080224DDD010 | float | YES | | NULL | | 
| 5600080224E7FE10 | float | YES | | NULL | | 
| 0500080224D40B10 | float | YES | | NULL | | 
+------------------+----------+------+-----+---------------------+-------+ 

内容如下:

mysql> select * from logtemp limit 10; 
+---------------------+------------------+------------------+------------------+------------------+ 
| DATETIME | 0400080224D59710 | CA00080224DDD010 | 5600080224E7FE10 | 0500080224D40B10 | 
+---------------------+------------------+------------------+------------------+------------------+ 
| 2013-11-01 12:58:01 | 25.75 | 24.19 | 24.31 | 24.44 | 
| 2013-11-01 12:59:03 | 25.81 | 24.19 | 24.31 | 24.44 | 
| 2013-11-01 13:00:05 | 25.94 | 24.25 | 24.38 | 24.44 | 
| 2013-11-01 13:01:07 | 25.94 | 24.25 | 24.38 | 24.44 | 
| 2013-11-01 13:02:08 | 25.94 | 24.31 | 24.38 | 24.5 | 
| 2013-11-01 13:03:10 | 26.06 | 24.31 | 24.38 | 24.5 | 
| 2013-11-01 13:04:11 | 26.19 | 24.31 | 24.44 | 24.56 | 
| 2013-11-01 13:05:13 | 26.31 | 24.31 | 24.44 | 24.56 | 
| 2013-11-01 13:06:14 | 26.38 | 24.31 | 24.44 | 24.56 | 
| 2013-11-01 13:07:16 | 26.38 | 24.31 | 24.44 | 24.56 | 
+---------------------+------------------+------------------+------------------+------------------+ 

列名(0400080224D59710 等)是温度传感器的唯一 ROM ID。连接新传感器时，将在数据库中创建一个附加列 - 将传感器的 ROM ID 作为列名。

现在，我添加了另一个表格，如下所示:

mysql> show columns from sensoren; 
+--------------------------+--------------+------+-----+---------+-------+ 
| Field | Type | Null | Key | Default | Extra | 
+--------------------------+--------------+------+-----+---------+-------+ 
| strSensorHex | varchar(16) | NO | PRI | NULL | | 
| strSensorBeschreibung | varchar(100) | NO | MUL | NULL | | 
| strSensorRRDTabellenName | varchar(25) | NO | | NULL | | 
+--------------------------+--------------+------+-----+---------+-------+ 

这个表的内容是:

mysql> select * from sensoren; 
+------------------+-----------------------+--------------------------+ 
| strSensorHex | strSensorBeschreibung | strSensorRRDTabellenName | 
+------------------+-----------------------+--------------------------+ 
| 0400080224D59710 | Testsensor 1 | TEST1 | 
| CA00080224DDD010 | Testsensor 2 | TEST2 | 
| 5600080224E7FE10 | Testsensor 3 | TEST3 | 
| 0500080224D40B10 | Testsensor 4 | TEST4 | 
+------------------+-----------------------+--------------------------+ 

strSensorHex 列包含温度传感器的唯一 ROM ID。

现在我想创建一个包含以下信息的新 View :

DATETIME, strSensorHex, strSensorBeschreibung, Temperatur value

我已经尝试通过谷歌搜索有关如何实现这一点的信息，但我还找不到答案，而且我对 SQL 语法不是很熟悉:-(

Answer 1

一个可能的解决方案是使用动态 SQL 和存储过程而不是 View

DELIMITER $$
CREATE PROCEDURE sensortemp()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(CONCAT(
           'WHEN s.strSensorHex = ''', strSensorHex, ''' THEN `', strSensorHex, '`')
         ORDER BY strSensorHex SEPARATOR ' ')
    INTO @sql
    FROM sensoren;

  SET @sql = CONCAT(
               'SELECT l.datetime, s.strSensorHex, s.strSensorBeschreibung, CASE ', @sql, 'END temperatur
                  FROM logtemp l CROSS JOIN sensoren s
                 ORDER BY l.datetime, s.strSensorBeschreibung'
             );

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELiMITER ;

示例用法:

CALL sensortemp();

示例输出:

|            DATETIME |     STRSENSORHEX | STRSENSORBESCHREIBUNG | TEMPERATUR ||---------------------|------------------|-----------------------|------------|| 2013-11-01 12:58:01 | 0400080224D59710 |          Testsensor 1 |      25.75 || 2013-11-01 12:58:01 | CA00080224DDD010 |          Testsensor 2 |      24.19 || 2013-11-01 12:58:01 | 5600080224E7FE10 |          Testsensor 3 |      24.31 || 2013-11-01 12:58:01 | 0500080224D40B10 |          Testsensor 4 |      24.44 || 2013-11-01 12:59:03 | 0400080224D59710 |          Testsensor 1 |      25.81 || 2013-11-01 12:59:03 | CA00080224DDD010 |          Testsensor 2 |      24.19 || 2013-11-01 12:59:03 | 5600080224E7FE10 |          Testsensor 3 |      24.31 || 2013-11-01 12:59:03 | 0500080224D40B10 |          Testsensor 4 |      24.44 || 2013-11-01 13:00:05 | 0400080224D59710 |          Testsensor 1 |      25.94 || 2013-11-01 13:00:05 | CA00080224DDD010 |          Testsensor 2 |      24.25 || 2013-11-01 13:00:05 | 5600080224E7FE10 |          Testsensor 3 |      24.38 || 2013-11-01 13:00:05 | 0500080224D40B10 |          Testsensor 4 |      24.44 |...

Here is SQLFiddle demo

---

UPDATE: Based on your comments: you can use above mentioned code to help you build a definition for a view (basically what Gordon Linoff suggested in his answer). The definition of your view might look like

CREATE VIEW vw_sensorentemp AS
  SELECT l.datetime, s.strSensorHex, s.strSensorBeschreibung, 
         CASE s.strSensorHex 
           WHEN '0400080224D59710' THEN `0400080224D59710` 
           WHEN '0500080224D40B10' THEN `0500080224D40B10` 
           WHEN '5600080224E7FE10' THEN `5600080224E7FE10` 
           WHEN 'CA00080224DDD010' THEN `CA00080224DDD010` 
         END temperatur 
  FROM logtemp l CROSS JOIN sensoren s;

现在您可以使用它对结果进行不同的排序

SELECT * 
  FROM vw_sensorentemp
 ORDER BY strSensorBeschreibung DESC;

或加入其他表。

注意:每次添加新传感器或删除一些传感器时，您都必须更新 View 的定义。

这是 SQLFiddle 该场景的工作演示。