设为首页 加入收藏

TOP

MYSQL做Pivot Table的实例(一)
2018-01-16 14:49:47 】 浏览:216
Tags:MYSQL Pivot Table 实例

"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 !!):

mysql> select name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams group by name;

+------+---
首页 上一页 1 2 3 下一页 尾页 1/3/3
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇如何实现在线更新SQL Server千万.. 下一篇查看Oracle数据库名和实例名的命令

最新文章

热门文章

Hot 文章

Python

C 语言

C++基础

大数据基础

linux编程基础

C/C++面试题目