select sum(case when job='cleark' then name end) as cleark, sum(case when job='manager' then name end) as manager, sum(case when job='analyst' then name end) as analyst from emp2 group by job
http://blog.csdn.net/acmain_chm/article/details/4283943 MySQL交叉表 在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...
1 SELECT SUM(IF(Deptno=10,cnt,0)),SUM(IF(Deptno=20,cnt,0)), SUM(IF(Deptno=30,cnt,0)) FROM TT
2 SET @A=''; SET @B=0; SELECT bz,MAX(IF(JOB='cleark',`name`,'')) AS cleark ,MAX(IF(JOB='manager',`name`,'')) AS cleark ,MAX(IF(JOB='analyst',`name`,'')) AS cleark FROM ( SELECT *,@B:=IF(@A=JOB,@B+1,1) AS BZ,@A:=JOB FROM TTY A) b GROUP BY bz
sum(case when job='manager' then name end) as manager,
sum(case when job='analyst' then name end) as analyst
from emp2
group by job
MySQL交叉表
在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...
SELECT SUM(IF(Deptno=10,cnt,0)),SUM(IF(Deptno=20,cnt,0)),
SUM(IF(Deptno=30,cnt,0))
FROM TT
SET @A='';
SET @B=0;
SELECT bz,MAX(IF(JOB='cleark',`name`,'')) AS cleark
,MAX(IF(JOB='manager',`name`,'')) AS cleark
,MAX(IF(JOB='analyst',`name`,'')) AS cleark
FROM (
SELECT *,@B:=IF(@A=JOB,@B+1,1) AS BZ,@A:=JOB FROM TTY A) b
GROUP BY bz