简化一下这句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 1 Else 0 End)l
from xz_jbxxb a ,sys_xzxh b where a.xx_id=b.xx_id group by a.xx_id,b.xx_name
[解决办法]
另一种方法：
sum(case month(xz_txrq) when '1 ' then 数量 else 0 end) as '1月份箱子的数量 ',
sum(case month(xz_txrq) when '2 ' then 数量 else 0 end) as '2月份箱子的数量 ',
...
