急！高手来帮忙：日期分组统计
现在要实现这样一个统计功能。
统计某一年份里面每个客户签的合同总数。
比如说2006年内每一个公司客户所签合同的总数。
如果我加了WHERE   YEAR(SignDate)   =   2006,那么出来的就只有年份是2006的客户了，但是我希望就算2006年没有合同的客户也要显示在结果里面，就是说显示0吧。
大家帮忙看看阿。
select   CustomerName,Count(*)   AS   TotalPiece
from   TQof
where   CompanyID   =   '10001 '
Group   by   CustomerName

------解决方案--------------------
select tmpA.CustomerName, TotalPiece=isnull(tmpB.TotalPiece, 0)
from
(
select distinct CustomerName from TQof
)tmpA
left join
(
select CustomerName,Count(*) AS TotalPiece
from TQof
where CompanyID = '10001 '
Group by CustomerName
)tmpB on tmpA.CustomerName=tmpB.CustomerName
------解决方案--------------------
select a.CustomerName ,isnull(b.TotalPiece,0) as TotalPiece
(select distinct CustomerName from TQof) a
left join
(
select CustomerName,Count(*) AS TotalPiece
from TQof
where YEAR(SignDate) = 2006
Group by CustomerName
) b
on a.CustomerName =b.CustomerName


*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码)

最新版本：20070130

http://www.cnblogs.com/feiyun0112/archive/2006/09/20/509783.html
------解决方案--------------------
需要用表变量或临时表实现:
declare @ta table ([date] datetime, [count] int)
insert @ta select '2004-1-1 ', 1
union all select '2007-1-2 ', 2
union all select '2007-1-4 ', 3


declare @start datetime,@end datetime
select @start=min(date) from @ta
select @end=max(date) from @ta
declare @tmp table(date varchar(10))
while year(@start)!> year(@end)
begin
insert @tmp select year(@start)
set @start=dateadd(year,1,@start)
end
select tb.date,[count]=sum(isnull(count,0))
from @tmp tb left join @ta ta on year(tb.date)=year(ta.date)
group by tb.date

date count
---------- -----------
2004 1
2005 0
2006 0
2007 5

(4 行受影响)