sqlServer中分类报表sql技巧，很实用

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

有表tableA如下：-----------------------------------------------------id,   type,  countNumber,  countDate1 A 10  2005-02-0121:23:342 B 8  2005-02-0121:23:343 C 5  2005-02-0121:23:344 B 4  2005-02-0111:23:345 C 5  2005-02-0122:23:346 A 11  2005-02-0212:23:347 B 9  2005-02-0214:23:348 C 8  2005-02-0217:23:349 A 15  2005-02-0219:23:3410 C 6  2005-02-0204:23:3411 A 7  2005-02-031:23:3412 B 11  2005-02-032:23:3413 C 12  2005-02-035: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 ,(selectsum(*)from tableAwheretype='A'andconvert(char(10),countDate,20)=a.DATEFMT)typeA ,(selectsum(*)from tableAwheretype='B'andconvert(char(10),countDate,20)=a.DATEFMT)typeB ,(selectsum(*)from tableAwheretype='C'andconvert(char(10),countDate,20)=a.DATEFMT)typeC

FROM( SELECTconvert(char(10),countDate,20)DATEFMT FROM tableA GROUPBYconvert(char(10),countDate,20))a

但是这条语句在sqlServer上执行起来特别的慢，要很久(20多秒)，tabelA中有9万条数据.而我在mysql(表中有2万条数据)上执行就快很多！

后来在论坛上看到高手这样写

selectconvert(char(10),countDate,20),typeA=sum(casetypewhenAthenCountNumberelse0end),typeB=sum(casetypewhenBthenCountNumberelse0end),typeC=sum(casetypewhenCthenCountNumberelse0end)FromtableAgroupbyconvert(char(10),countDate,20)

只用1秒不到！ 唉！厉害厉害！
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>