sql语句优化问题

[tianyuyi123]

SELECT aa.税务登记号, aa.单位名称, (select Sum(金额) from xqwy where 缴库日期>=#2005/1/1# and 缴库日期<=#2005/12/31# and 税务登记号=aa.税务登记号 and 单位名称=aa.单位名称) AS 2005,(select Sum(金额) from xqwy where 缴库日期>=#2006/1/1# and 缴库日期<=#2006/12/31# and 税务登记号=aa.税务登记号 and 单位名称=aa.单位名称) AS 2006,(select Sum(金额) from xqwy where 缴库日期>=#2007/1/1# and 缴库日期<=#2007/12/31# and 税务登记号=aa.税务登记号 and 单位名称=aa.单位名称) AS 2007,(select Sum(金额) from xqwy where 缴库日期>=#2008/1/1# and 缴库日期<=#2008/12/31# and 税务登记号=aa.税务登记号 and 单位名称=aa.单位名称 ) AS 2008,(select Sum(金额) from xqwy where 缴库日期>=#2009/1/1# and 缴库日期<=#2009/12/31# and 税务登记号=aa.税务登记号 and 单位名称=aa.单位名称) AS 2009,(select Sum(金额) from xqwy where 缴库日期>=#2010/1/1# and 缴库日期<=#2010/12/31# and 税务登记号=aa.税务登记号 and 单位名称=aa.单位名称) AS 2010,Sum(aa.金额) as 金额, First(aa.所属分局) AS 所属分局 FROM xqwy as aa GROUP BY aa.税务登记号, aa.单位名称;

这个sql语句现在执行下要好几分钟 数据库里有近10万条数据吧
有谁能帮忙优化下

[dawugui]

SELECT aa.税务登记号, aa.单位名称, 
  sum(case when 缴库日期>=#2005/1/1# and 缴库日期 <=#2005/12/31# then 金额 else 0 end) AS 2005,
  sum(case when 缴库日期>=#2006/1/1# and 缴库日期 <=#2006/12/31# then 金额 else 0 end) AS 2006,
  ...
  sum(case when 缴库日期>=#2010/1/1# and 缴库日期 <=#2010/12/31# then 金额 else 0 end) AS 2010,
  Sum(金额) as 金额, 
  First(所属分局) AS 所属分局 
FROM xqwy 
GROUP BY 税务登记号, 单位名称; 

--不知道你的那个版本里面是否支持year函数
SELECT aa.税务登记号, aa.单位名称, 
  sum(case year(缴库日期) when 2005 then 金额 else 0 end) AS 2005,
  sum(case year(缴库日期) when 2006 then 金额 else 0 end) AS 2006,
  ...
  sum(case year(缴库日期) when 2010 then 金额 else 0 end) AS 2010,
  Sum(金额) as 金额, 
  First(所属分局) AS 所属分局 
FROM xqwy 
GROUP BY 税务登记号, 单位名称; 

[tianyuyi123]

用的是access数据库

[dawugui]

[Quote=引用 2 楼 tianyuyi123 的回复:]
用的是access数据库
[/Quote]
去access那个版问问.

[htl258_Tony]

access的经常发错版块.

[tianyuyi123]

本人自己已经搞定
我把case when换成iif语句了 呵呵