如何用MYSQL 做 Pivot Table

如何用MYSQL 做 Pivot Table

这很简单啊！ 在windows下，假如你的mysql装在 d:\mysql 就可以这样： 先切换到它的bin目录 cd d:\mysql\bin\ d:\mysql\bin>mysql -u root -p 接着输入你的root密码 接下来你就可以创建数据库、创建用户、创建修改表之类的操作（常用命令如下）

"pivot table" or a "crosstab report" (Note: this page needs to be wikified) SQL Characteristic Functions: Do it without "if", "case", or "GROUP_CONCAT". Yes, there is use for this..."if" statements sometimes cause problems when used in combination. The simple secret, and it's also why they work in almost all databases, is the following functions: o sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0 respectively o abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0 o 1-abs( sign( x) ) complement of the above, since this returns 1 only if x = 0 Quick example: sign(-1) = -1, abs( sign(-1) ) = 1, 1-abs( sign(-1) ) = 0 Data for full example: CREATE TABLE exams ( pkey int(11) NOT NULL auto_increment, name varchar(15), exam int, score int, PRIMARY KEY (pkey) ); insert into exams (name,exam,score) values ('Bob',1,75); insert into exams (name,exam,score) values ('Bob',2,77); insert into exams (name,exam,score) values ('Bob',3,78); insert into exams (name,exam,score) values ('Bob',4,80); insert into exams (name,exam,score) values ('Sue',1,90); insert into exams (name,exam,score) values ('Sue',2,97); insert into exams (name,exam,score) values ('Sue',3,98); insert into exams (name,exam,score) values ('Sue',4,99); mysql> select * from exams; +------+------+------+-------+ | pkey | name | exam | score | +------+------+------+-------+ | 1 | Bob | 1 | 75 | | 2 | Bob | 2 | 77 | | 3 | Bob | 3 | 78 | | 4 | Bob | 4 | 80 | | 5 | Sue | 1 | 90 | | 6 | Sue | 2 | 97 | | 7 | Sue | 3 | 98 | | 8 | Sue | 4 | 99 | +------+------+------+-------+ 8 rows in set (0.00 sec) mysql> select name, sum(score*(1-abs(sign(exam-1)))) as exam1, sum(score*(1-abs(sign(exam-2)))) as exam2, sum(score*(1-abs(sign(exam-3)))) as exam3, sum(score*(1-abs(sign(exam-4)))) as exam4 from exams group by name; +------+-------+-------+-------+-------+ | name | exam1 | exam2 | exam3 | exam4 | +------+-------+-------+-------+-------+ | Bob | 75 | 77 | 78 | 80 | | Sue | 90 | 97 | 98 | 99 | +------+-------+-------+-------+-------+ 2 rows in set (0.00 sec) Note, the above pivot table was created with one select statement. Let's decompose to make the trick clearer, for the second exam: mysql> select name, score, exam, exam-2, sign(exam-2), abs(sign(exam-2)), 1-abs(sign(exam-2)), score*(1-abs(sign(exam-2))) as exam2 from exams; +------+-------+------+--------+--------------+-------------------+---------------------+-------+ | name | score | exam | exam-2 | sign(exam-2) | abs(sign(exam-2)) | 1-abs(sign(exam-2)) | exam2 | +------+-------+------+--------+--------------+-------------------+---------------------+-------+ | Bob | 75 | 1 | -1 | -1 | 1 | 0 | 0 | | Bob | 77 | 2 | 0 | 0 | 0 | 1 | 77 | | Bob | 78 | 3 | 1 | 1 | 1 | 0 | 0 | | Bob | 80 | 4 | 2 | 1 | 1 | 0 | 0 | | Sue | 90 | 1 | -1 | -1 | 1 | 0 | 0 | | Sue | 97 | 2 | 0 | 0 | 0 | 1 | 97 | | Sue | 98 | 3 | 1 | 1 | 1 | 0 | 0 | | Sue | 99 | 4 | 2 | 1 | 1 | 0 | 0 | +------+-------+------+--------+--------------+-------------------+---------------------+-------+ 8 rows in set (0.00 sec) You may think IF's would be clean but WATCH OUT! Look what the following gives (INCORRECT !!):