Mysql column Change statistics query, row to column statistics query

I read a blog post very well, it is easy to understand after reading, the post address is: http://www.cnblogs.com/lhj588/p/3315876.html

Recently I met a friend in the group and said, so record:

Assume that the table name is T. There are six fields in the list p1,p2,p3,s1,s2,s3

Now want

Sum of S1 values when p1=100

Sum of S2 values when p2=100

Sum of S3 values when p3=100

To create a table:

CREATE TABLE ' t ' (' ID ' int (one) not null auto_increment, ' s1 ' int (one) default null, ' s2 ' int (one) default null, ' S3 ' in T (one) default null, ' P1 ' int (one) default null, ' P2 ' int (one) default null, ' p3 ' int (one) default NULL, PRIMARY KEY (' id ') )) Engine=myisam auto_increment=18 DEFAULT charset=utf-8;

Add some data:

INSERT into T value (NULL, Floor (rand ()%10*100), Floor (rand ()%10*100), Floor (rand ()%10*100), 100,100,100);

Reference answer (MySQL row to column statistics):

SELECT SUM (case p1 if S1 else 0 end) S1sum,sum (case P2 when the S2 ELSE 0 end) S2sum,sum (case P3 when 100 Then S3 ELSE 0 END) s3sumfrom ' t ';

In the end I compare the statistics is correct

Select SUM (S1) from t where p1 = 100;select sum (s2) from t where p2 = 100;select sum (s3) from t where p3 = 100;

This article is from the "happy Programming _ Endless" blog, please be sure to keep this source http://happyliu.blog.51cto.com/501986/1734708

Mysql column Change statistics query, row to column statistics query