• 博客访问： 689892
• 博文数量： 95
• 博客积分： 1754
• 博客等级： 上尉
• 技术积分： 1607
• 用 户 组： 普通用户
• 注册时间： 2010-03-12 10:06

2015年（3）

2013年（15）

2012年（77）

2012-12-13 15:12: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;