注册 登录
编程论坛 SQL Server论坛

求助~~如何实现分组统计的时候,没有记录的组可以显示为0,而不是不显示

leicumt 发布于 2010-08-23 22:24, 654 次点击
按照TheYear分组,统计每年Numbers<60的条数;
select TheYear,Count(*) from Test Where Numbers<60 Group BY TheYear
得到的结果是:
TheYear Numbers
1999 1


如何才能得到
TheYear Numbers
1999 1
2000 0



PS:我用ISNULL(count(*),0)也没实现想要的效果~~~请各位大侠出手相助~~

表结构如下:
CREATE TABLE [dbo].[Test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TheYear] [int] NULL ,
[Numbers] [int] NULL  
) ON [PRIMARY]
GO
insert into Test(TheYear,TotalScore) values (1999,87)
insert into Test(TheYear,TotalScore) values (1999,33)
insert into Test(TheYear,TotalScore) values (2000,85)
insert into Test(TheYear,TotalScore) values (2000,89)
3 回复
#2
gameohyes2010-08-23 22:41
select TheYear,次数=count(case when Numbers<60 then Numbers end) from Test group by TheYear
#3
qingshuiliu2010-08-23 22:57
select t.TheYear,sum(t.Number) as Number from
(select TheYear,Count(*) as Number from Test Where Numbers<60 Group BY TheYear
union all
select TheYear,0 from Test) as t
group by t.TheYear

#4
aei1352010-08-24 09:31
select theyear,count(case when numbers<60 then numbers else null end) from test group by theyear
1