mysql - mysql中的数据透视表

Question

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

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;

Answer 1

你总是可以创建一个 shell 脚本来完全做到这一点:-)

#!/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

---

数据:

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 |
+------+------+------+------+---------------------+