有表 tablea 如下：-----------------------------------------------------id, type , countnumber , countdate1 a 10 2005-02-01 21:23:342 b 8 2005-02-01 21:23:343 c 5 2005-02-01 21:23:344 b 4 2005-02-01 11:23:345 c 5 2005-02-01 22:23:346 a 11 2005-02-02 12:23:347 b 9 2005-02-02 14:23:348 c 8 2005-02-02 17:23:349 a 15 2005-02-02 19:23:3410 c 6 2005-02-02 04:23:3411 a 7 2005-02-03 1:23:3412 b 11 2005-02-03 2:23:3413 c 12 2005-02-03 5:23:34.......................
想要用一条语句生成如下的报表
日期 类型a 类型b 类型c 2005-02-01 10 12 10 2005-02-02 26 9 14 2005-02-03 7 11 12
我用的是如下的sql语句:
select a.datefmt ,(select sum(*) from tablea where type='a' and convert(char(10),countdate,20)= a.datefmt) typea ,(select sum(*) from tablea where type='b' and convert(char(10),countdate,20)= a.datefmt) typeb ,(select sum(*) from tablea where type='c' and convert(char(10),countdate,20)= a.datefmt) typec
from ( select convert(char(10),countdate,20) datefmt from tablea group by convert(char(10),countdate,20)) a
但是这条语句在sqlserver 上执行起来特别的慢，要很久(20多秒)，tabela 中有9万条数据. 而我在mysql(表中有2万条数据)上执行就快很多！
后来在论坛上看到高手这样写
select convert(char(10),countdate,20),typea=sum(case type when a then countnumber else 0 end),typeb=sum(case type when b then countnumber else 0 end),typec=sum(case type when c then countnumber else 0 end)from tableagroup by convert(char(10),countdate,20)
只用 1秒 不到！ 唉！ 厉害厉害！
