mysql中的行转列_MySQL行转列实现

工作中应该开发的要求，需要对1月到12月的数据用列的方式表示出来。

表:

CREATE TABLE orders(cid INT,cname VARCHAR(50),createdate DATETIME)

SQL语句实现：

SELECT

b.cname,b.cid,

MAX(b.MaxTimeZuoGui) MaxTimeZuoGui,MIN(b.MinTimeZuoGui) MinTimeZuoGui,

SUM(b.month1) month1,SUM(b.month2) month2,

SUM(b.month3) month3,SUM(b.month4) month4,

SUM(b.month5) month4,SUM(b.month6) month6,

SUM(b.month7) month7,SUM(b.month8) month8,

SUM(b.month9) month9,SUM(b.month10) month10,

SUM(b.month11) month11,SUM(b.month12) month12,

SUM(b.month1+b.month2+b.month3+b.month4+b.month5+b.month6+b.month7+b.month8+b.month9+b.month10+b.month11+b.month12) AmountCount

FROM

(SELECT a.cid cid ,a.cname cname,a.MaxTimeZuoGui,a.MinTimeZuoGui,

CASE WHEN mondate=1 THEN counts ELSE 0 END month1,

CASE WHEN mondate =2 THEN counts ELSE 0 END month2,

CASE WHEN mondate =03 THEN counts ELSE 0 END month3,

CASE WHEN mondate =04 THEN counts ELSE 0 END month4,

CASE WHEN mondate =05 THEN counts ELSE 0 END month5,

CASE WHEN mondate =06 THEN counts ELSE 0 END month6,

CASE WHEN mondate =07 THEN counts ELSE 0 END month7,

CASE WHEN mondate =08 THEN counts ELSE 0 END month8,

CASE WHEN mondate =09 THEN counts ELSE 0 END month9,

CASE WHEN mondate =10 THEN counts ELSE 0 END month10,

CASE WHEN mondate =11 THEN counts ELSE 0 END month11,

CASE WHEN mondate =12 THEN counts ELSE 0 END month12

FROM (SELECT cid,cname,COUNT(*) counts,

MAX(createdate) MaxTimeZuoGui,

MIN(createdate) MinTimeZuoGui,DATE_FORMAT(createdate,'%m') mondate

FROM orders

GROUP BY cid,cname,DATE_FORMAT(createdate,'%m')) a) b

GROUP BY b.cname,b.cid;