简化一下这句SQL
select   b.xx_name,
(select   count(*)   from   xz_jbxxb   where     xx_id=a.xx_id   and   month(xz_txrq)= '1 '   and   year(xz_txrq)= '2007 ')a,
(select   count(*)   from   xz_jbxxb   where     xx_id=a.xx_id   and   month(xz_txrq)= '2 '   and   year(xz_txrq)= '2007 ')b,
(select   count(*)   from   xz_jbxxb   where     xx_id=a.xx_id   and   month(xz_txrq)= '3 '   and   year(xz_txrq)= '2007 ')c,
(select   count(*)   from   xz_jbxxb   where     xx_id=a.xx_id   and   month(xz_txrq)= '4 '   and   year(xz_txrq)= '2007 ')d,
(select   count(*)   from   xz_jbxxb   where     xx_id=a.xx_id   and   month(xz_txrq)= '5 '   and   year(xz_txrq)= '2007 ')e,
(select   count(*)   from   xz_jbxxb   where     xx_id=a.xx_id   and   month(xz_txrq)= '6 '   and   year(xz_txrq)= '2007 ')f,
(select   count(*)   from   xz_jbxxb   where     xx_id=a.xx_id   and   month(xz_txrq)= '7 '   and   year(xz_txrq)= '2007 ')g,
(select   count(*)   from   xz_jbxxb   where     xx_id=a.xx_id   and   month(xz_txrq)= '8 '   and   year(xz_txrq)= '2007 ')h,
(select   count(*)   from   xz_jbxxb   where     xx_id=a.xx_id   and   month(xz_txrq)= '9 '   and   year(xz_txrq)= '2007 ')i,
(select   count(*)   from   xz_jbxxb   where     xx_id=a.xx_id   and   month(xz_txrq)= '10 '   and   year(xz_txrq)= '2007 ')j,
(select   count(*)   from   xz_jbxxb   where     xx_id=a.xx_id   and   month(xz_txrq)= '11 '   and   year(xz_txrq)= '2007 ')k,
(select   count(*)   from   xz_jbxxb   where     xx_id=a.xx_id   and   month(xz_txrq)= '12 '   and   year(xz_txrq)= '2007 ')l
from   xz_jbxxb   a   ,sys_xzxh   b   where     a.xx_id=b.xx_id   group   by   a.xx_id,b.xx_name


按月分查询箱子的数量.

------解决方案--------------------
try

select b.xx_name,
SUM(Case When month(xz_txrq)= '1 ' and year(xz_txrq)= '2007 ' Then 1 Else 0 End)a,
SUM(Case When month(xz_txrq)= '2 ' and year(xz_txrq)= '2007 ' Then 1 Else 0 End)b,
SUM(Case When month(xz_txrq)= '3 ' and year(xz_txrq)= '2007 ' Then 1 Else 0 End)c,
SUM(Case When month(xz_txrq)= '4 ' and year(xz_txrq)= '2007 ' Then 1 Else 0 End)d,
SUM(Case When month(xz_txrq)= '5 ' and year(xz_txrq)= '2007 ' Then 1 Else 0 End)e,
SUM(Case When month(xz_txrq)= '6 ' and year(xz_txrq)= '2007 ' Then 1 Else 0 End)f,
SUM(Case When month(xz_txrq)= '7 ' and year(xz_txrq)= '2007 ' Then 1 Else 0 End)g,
SUM(Case When month(xz_txrq)= '8 ' and year(xz_txrq)= '2007 ' Then 1 Else 0 End)h,
SUM(Case When month(xz_txrq)= '9 ' and year(xz_txrq)= '2007 ' Then 1 Else 0 End)i,
SUM(Case When month(xz_txrq)= '10 ' and year(xz_txrq)= '2007 ' Then 1 Else 0 End)j,
SUM(Case When month(xz_txrq)= '11 ' and year(xz_txrq)= '2007 ' Then 1 Else 0 End)k,
SUM(Case When month(xz_txrq)= '12 ' and year(xz_txrq)= '2007 ' Then