mysql枢轴/交叉表查询
mysql枢轴/交叉表查询
提问于 2012-09-12 07:02:39
问题1: I有一个具有以下结构和数据的表：
app_id transaction_id mobile_no node_id customer_attribute entered_value
100 111 9999999999 1 Q1 2
100 111 9999999999 2 Q2 1
100 111 9999999999 3 Q3 4
100 111 9999999999 4 Q4 3
100 111 9999999999 5 Q5 2
100 222 8888888888 4 Q4 1
100 222 8888888888 3 Q3 2
100 222 8888888888 2 Q2 1
100 222 8888888888 1 Q1 3
100 222 8888888888 5 Q5 4 我想以以下格式显示这些记录：
app_id | transaction_id | mobile | Q1 | Q2 | Q3 | Q4 | Q5 |
100 | 111 | 9999999999 | 2 | 1 | 4 | 3 | 2 |
100 | 222 | 8888888888 | 3 | 1 | 2 | 1 | 4 |我知道我需要使用交叉表/枢轴查询来获得这个显示。为此，我在有限的知识基础上尝试了它。以下是我的查询：
SELECT app_id, transaction_id, mobile_no,
(CASE node_id WHEN 1 THEN entered_value ELSE '' END) AS user_input1,
(CASE node_id WHEN 2 THEN entered_value ELSE '' END) AS user_input2,
(CASE node_id WHEN 3 THEN entered_value ELSE '' END) AS user_input3,
(CASE node_id WHEN 4 THEN entered_value ELSE '' END) AS user_input4,
(CASE node_id WHEN 5 THEN entered_value ELSE '' END) AS user_input5
FROM trn_user_log
GROUP BY app_id, transaction_id, mobile_no, node_id基于这个查询，我得到了下面的显示：
app_id transaction_id mobile_no user_input1 user_input2 user_input3 user_input4 user_input5
100 111 9999999999 2
100 111 9999999999 1
100 111 9999999999 4
100 111 9999999999 3
100 111 9999999999 2
100 222 8888888888 3
100 222 8888888888 1
100 222 8888888888 2
100 222 8888888888 1
100 222 8888888888 4 有人能帮我做适当的更改吗?我需要对我的查询进行适当的更改，以便在一个行中获得记录，而不是像上面那样多行。
问题2:也有一种方法可以将特定字段的值作为列名。如你所见，我有user_input1，user_input2，.作为头像。相反，我希望将customer_attribute中的值作为列的标题。
为此，我检查了NAME_CONST(name,value)如下：
SELECT app_id, transaction_id, mobile_no,
NAME_CONST(customer_attribute, (CASE node_id WHEN 1 THEN entered_value ELSE '' END))
FROM trn_user_log 但它给出了一个错误
Error Code : 1210 Incorrect arguments to NAME_CONST需要帮助。
回答 3
Stack Overflow用户
回答已采纳
发布于 2012-09-12 10:27:09
虽然@John的静态答案很好用，但是如果要转换的列数量不多，我会考虑使用准备好的语句来获得结果：
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'GROUP_CONCAT((CASE node_id when ',
node_id,
' then entered_value else NULL END)) AS user_input',
node_id
)
) INTO @sql
FROM trn_user_log;
SET @sql = CONCAT('SELECT app_id, transaction_id, mobile_no, ', @sql, '
FROM trn_user_log
GROUP BY app_id, transaction_id, mobile_no');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;请参阅与Demo
至于你的第二个，请澄清你想做什么，这是不清楚的。
Stack Overflow用户
发布于 2012-09-12 07:05:28
在GROUP_CONCAT子句中添加CASE
SELECT app_id, transaction_id, mobile_no,
GROUP_CONCAT((CASE node_id WHEN 1 THEN entered_value ELSE NULL END)) AS user_input1,
GROUP_CONCAT((CASE node_id WHEN 2 THEN entered_value ELSE NULL END)) AS user_input2,
GROUP_CONCAT((CASE node_id WHEN 3 THEN entered_value ELSE NULL END)) AS user_input3,
GROUP_CONCAT((CASE node_id WHEN 4 THEN entered_value ELSE NULL END)) AS user_input4,
GROUP_CONCAT((CASE node_id WHEN 5 THEN entered_value ELSE NULL END)) AS user_input5
FROM trn_user_log
GROUP BY app_id, transaction_id, mobile_nohttp://sqlfiddle.com/#!2/7cbde/4
Stack Overflow用户
发布于 2014-01-12 18:03:37
@“黑暗骑士扇”，对于我正在做的一项任务来说，这是一个非常有用的问题。为了解决你的第二个问题，我修改了“蓝鳍金枪鱼”的解决方案。下面的代码生成最初请求的格式，其值为customer_attribute作为交叉选项卡中的列标题。
有关的改变是：
' then entered_value else NULL END)) AS user_input',
node_id对此：
' then entered_value else NULL END)) AS ''',
customer_attribute,''''完整的代码：
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'GROUP_CONCAT((CASE node_id when ',
node_id,
' then entered_value else NULL END)) AS ''',
customer_attribute,''''
)
) INTO @sql
FROM trn_user_log;
SET @sql = CONCAT('SELECT app_id, transaction_id, mobile_no, ', @sql, '
FROM trn_user_log
GROUP BY app_id, transaction_id, mobile_no');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;另外，对于浏览此问题的其他用户，如果您有许多值要跨选项卡，则可能会遇到错误，因为GROUP_CONCAT()的默认最大长度为1024个字符。为了增加这一点，把它放在你准备好的声明的开头：
SET SESSION group_concat_max_len = value; -- replace value with an int页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/12382771
复制相关文章
相似问题
腾讯云开发者
