34,838
社区成员




declare @TT table
(
areaID int identity(1,1) primary key,
[year] int
)
insert into @TT select 1999
insert into @TT select 1999
insert into @TT select 1999
insert into @TT select 2000
insert into @TT select 2000
insert into @TT select 2001
declare @T2 table
(
areaID int
)
insert into @T2 select 1
insert into @T2 select 1
insert into @T2 select 1
insert into @T2 select 2
insert into @T2 select 2
insert into @T2 select 4
insert into @T2 select 4
insert into @T2 select 4
insert into @T2 select 5
select T1.*,isnull(T2.areaCount,1) areaCount from
(
select [year],count([year]) yearCount from @TT group by [year]
)T1
left join
(
select [year],isnull(count(T2.areaID),1) areaCount from @T2 T2 join @TT T1 on T2.areaID=T1.areaID group by [year]
) T2
on T1.[year]=T2.[year]
year yearCount areaCount
----------- ----------- -----------
1999 3 5
2000 2 4
2001 1 1
/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-08 18:06:23.640●●●●●
★★★★★soft_wsx★★★★★
*/
if OBJECT_ID('t1') is not null drop table t1
create table t1(year1 nvarchar(10),areaid int identity(1,1) primary key)
insert t1(year1)
select'1999'
union all select'1999'
union all select'1999'
union all select'2000'
union all select'2000'
union all select'2001'
if OBJECT_ID('t2') is not null drop table t2
create table t2(areaid int)
insert t2(areaid)
select 1
union all select 1
union all select 1
union all select 2
union all select 2
union all select 4
union all select 4
union all select 4
union all select 5
select year1,SUM(areaCount) as areaCount,MAX(yearcount) as yearcount
from(
select x.year1,areaCount=COUNT(b.areaid),yearcount=(select count(1) from t1 where year1=x.year1 and areaid>=a.areaid)
from (select distinct year1 from t1) x left join t1 a
on x.year1=a.year1
left join t2 b
on a.areaid=b.areaid
group by x.year1,a.year1,a.areaid
) a group by year1
/*
year1 areaCount yearcount
1999 5 3
2000 4 2
2001 0 1
*/
2000方法
declare @TT table
(
areaID int identity(1,1) primary key,
[year] int
)
insert into @TT select 1999
insert into @TT select 1999
insert into @TT select 1999
insert into @TT select 2000
insert into @TT select 2000
insert into @TT select 2001
select [year],count([year]) yearCount from @TT group by [year]
year yearCount
----------- -----------
1999 3
2000 2
2001 1