mysql中的数据透视表 [英] pivot table in mysql

问题描述

我知道如何在mysql中创建数据透视表(请参见下面的代码示例)，但是如果数据透视表中的列数非常大并且我不想键入2000左右的标记名怎么办? -有没有一种方法可以生成该列表? 预先非常感谢.

I know how to make a pivot table in mysql (see code example below), but what if the number of columns in the pivot table is very large and I don't want to type 2000 or so tagnames? - Is there a way to have that list generated? Many thanks in advance.

drop table pivot;
create table pivot SELECT time,
       max(if(tagname = 'a', value, null)) AS 'a',
       max(if(tagname = 'b', value, null)) AS 'b',
       max(if(tagname = 'c', value, null)) AS 'c'
  FROM test where tagname in ('a','b','c')
GROUP BY time;
select * from pivot;

推荐答案

您始终可以创建一个完全可以做到这一点的shell脚本:-)

You can always create a shell script that does exactly that :-)

#!/bin/sh

mysql -BN test > /tmp/$$_tagnames.tmp <<SQL
select distinct tagname from test; 
SQL

cat > /tmp/$$_create_table.sql <<EOF
drop table if exists pivot;
create table pivot select 
EOF

while read tag; do
    echo "max(if(tagname = '$tag', value, null)) AS '$tag'," >> /tmp/$$_create_table.sql
done < /tmp/$$_tagnames.tmp

cat >> /tmp/$$_create_table.sql <<EOF
time
FROM test 
GROUP BY time;
select * from pivot;
EOF

mysql -Bt test < /tmp/$$_create_table.sql

rm /tmp/$$_create_table.sql
rm /tmp/$$_tagnames.tmp

---

数据:

---

Data:

mysql> select * from test;
+---------+-------+---------------------+
| tagname | value | time                |
+---------+-------+---------------------+
| a       | foo   | 2012-12-21 00:00:01 |
| b       | foo   | 2012-04-27 00:00:01 |
| c       | bar   | 2012-03-27 00:00:01 |
| d       | bar   | 2012-12-21 00:00:01 |
+---------+-------+---------------------+
4 rows in set (0.00 sec)

脚本输出:

$ ./pivot.sh 
+------+------+------+------+---------------------+
| a    | b    | c    | d    | time                |
+------+------+------+------+---------------------+
| NULL | NULL | bar  | NULL | 2012-03-27 00:00:01 |
| NULL | foo  | NULL | NULL | 2012-04-27 00:00:01 |
| foo  | NULL | NULL | bar  | 2012-12-21 00:00:01 |
+------+------+------+------+---------------------+

这篇关于mysql中的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！