我正在寻找一个按变量动态选择不同列名的查询。
假设我有一个包含我想选择的"动态列",类似于以下内容:
SELECT `ObjectID`,`ObjectLabel` FROM `modules_forms_objects`
+----------+---------------+|ObjectID|ObjectLabel|+----------+---------------+|71|加入日期||72 |活动|+----------+---------------+
现在,我又得到了一个表,它是由上面的"ObjectID"调用的列。此处:
SELECT `data_id`,`71`,`72` FROM `7`
+---------+---------------------+------+|data_id|71|72|+---------+---------------------+------+|1|0000-00-00 00:00:00|NULL|+---------+---------------------+------+
我想将一个"Value"列连接到第一个表,它将包含match第二个表中的列。(例如:对于ObjectID#72,该值将为NULL)。
最终,我希望我的结果是这样的:
+----------+---------------+--------------------+|ObjectID|ObjectLabel|值|+----------+---------------+--------------------+|71 |加入日期|0000-00-00 00:00:00||72|活动|NULL|+----------+---------------+--------------------+
当我使用传统的"JOIN"时,我得到了每行的所有列,这看起来有点重。
有什么想法吗?非常感谢!
基本静态查询可能看起来像这个
SELECT m.ObjectID, m.ObjectLabel, q.Value
FROM modules_forms_objects m LEFT JOIN
(
SELECT objectid,
CASE objectid
WHEN 71 THEN `71`
WHEN 72 THEN `72`
END value
FROM `7` t CROSS JOIN
(
SELECT 71 objectid UNION ALL
SELECT 72
) c
) q
ON m.objectid = q.objectid
这个想法是首先取消对7
表的透视,然后用modules_forms_objects
将其外部联接
输出:
|OBJECTID|OBJECTLABEL|VALUE|-----------|-------------|---------------------||71 |加入日期|0000-00-00 00:00:00||72|活动|(null)|
这是SQLFiddle演示
如果7
表中没有几十列,我建议您使用静态查询。
现在动态SQL 也是如此
SET @sql = NULL, @sql1 = NULL, @sql2 = NULL;
SELECT GROUP_CONCAT(
CONCAT('WHEN ''', column_name, ''' THEN `', column_name, '`')
SEPARATOR ' ')
INTO @sql1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = SCHEMA()
AND table_name = '7'
AND column_name LIKE '7%'
GROUP BY table_name;
SELECT GROUP_CONCAT(
CONCAT('SELECT ''', column_name, ''' objectid' )
SEPARATOR ' UNION ALL ')
INTO @sql2
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = SCHEMA()
AND table_name = '7'
AND column_name LIKE '7%'
GROUP BY table_name;
SET @sql = CONCAT(
'SELECT m.ObjectID, m.ObjectLabel, q.Value
FROM modules_forms_objects m LEFT JOIN
(
SELECT objectid,
CASE objectid ', @sql1, ' END value
FROM `7` t CROSS JOIN
(', @sql2, '
) c
) q
ON m.objectid = q.objectid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
这是SQLFiddle演示
使用SHOW COLUMNS FROM tab_name
并使用FIELD属性来获取相应的列。
这将为特定表中的所有列名提供
SELECT
GROUP_CONCAT(COLUMN_NAME)
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
table_name = 'mytable'