mysql行转列简单例子_Mysql实现行转列的简单应用

create table t_details(

t_id int PRIMARY KEY not null,

t_date DATE(50),

t_game varchar(50));

insert into t_details values(1,'2012-12-03','胜');

insert into t_details values(2,'2012-12-03','负');

insert into t_details values(3,'2012-12-03','胜');

insert into t_details values(4,'2012-12-03','负');

insert into t_details values(5,'2012-12-04','胜');

insert into t_details values(6,'2012-12-04','负');

insert into t_details values(7,'2012-12-04','胜');

select * from t_detail;

得到結果:

+------+------------+--------+

| t_id | t_date | t_game |

+------+------------+--------+

| 1 | 2012-12-03 | 胜 |

| 2 | 2012-12-03 | 负 |

| 3 | 2012-12-03 | 胜 |

| 4 | 2012-12-03 | 负 |

| 5 | 2012-12-04 | 胜 |

| 6 | 2012-12-04 | 负 |

| 7 | 2012-12-04 | 胜 |

+------+------------+--------+

預期結果：

+------------+----+----+

| t_date | 勝 | 負 |

+------------+----+----+

| 2012-12-03 | 2 | 2 |

| 2012-12-04 | 2 | 1 |

+------------+----+----+

实现语句：

select t_date,

sum(if(t_game='胜',1,0)) 勝,

sum(if(t_game='负',1,0)) 負

from t_details

group by t_date;

通过sum()計數實現數目查詢并實現行轉列，不需要子查詢。

其他方法：通過子查詢的,from@chenping2008

SELECT temp.t_date ,MAX(temp.胜) AS胜 ,MAX(temp.负) AS负FROM ( SELECTt_date ,CASEt_gameWHEN '胜' THEN COUNT(*)ELSE NULL

END AS '胜',CASEt_gameWHEN '负' THEN COUNT(*)ELSE NULL

END AS '负'

FROMt_detailsGROUP BYt_date ,

t_game

)AS temp

GROUP BY temp.t_date ;