超难的统计sql,求高手帮忙！！！

Question

aid   aname   bid   bname   cid   cname   date
1     中国    21    辽宁    342   大连    2011-04-11
1     中国    21    辽宁    111   沈阳    2011-03-11
1     中国    21    辽宁    121   鞍山    2010-09-09
1     中国    11    北京    100   北京    2011-01-09
1     中国    28    河北    456   石家庄  2010-09-01
1     中国    28    河北    466   唐山    2011-08-01如何通过sql取到以下结果
（cid   cname   date）是该省中市最新时间及对应的cid,cname      num（每个省多少个市）aid   aname   bid   bname   cid   cname   date        num
1     中国    21    辽宁    342   大连    2011-04-11  3
1     中国    11    北京    100   北京    2011-01-09  1
1     中国    28    河北    466   唐山    2011-08-01  2

Answer 1

select *,(select count(*) from tb where aid = t.aid and bid = t.bid) as num
from tb t
where not exists (select 1 from tb where aid = t.aid and bid = t.bid and date > t.date)

Answer 2

本帖最后由 josy 于 2011-06-22 20:45:21 编辑

  

Answer 3

select
 a.*,b.num
from
 tb a
join 
 (select aid,bid,count(distinct cid) as num from tb group by aid,bid) b
on
 a.aid=b.aid and a.bid=b.bid
where
 a.date=(select max(date) from tb where aid = a.aid and bid = a.bid )

Answer 4

create table tb(aid int,aname nvarchar(10),bid int,bname nvarchar(10),cid int,cname nvarchar(10),date datetime)
insert into tb select 1,'中国',21,'辽宁',342,'大连','2011-04-11'
insert into tb select 1,'中国',21,'辽宁',111,'沈阳','2011-03-11'
insert into tb select 1,'中国',21,'辽宁',121,'鞍山','2010-09-09'
insert into tb select 1,'中国',11,'北京',100,'北京','2011-01-09'
insert into tb select 1,'中国',28,'河北',456,'石家庄','2010-09-01'
insert into tb select 1,'中国',28,'河北',466,'唐山','2011-08-01'
go
select *,(select COUNT(*) from tb where bid=a.bid)num
from tb a 
where not exists(select 1 from tb where bid=a.bid and cid>a.cid)
go
drop table tb
/*
aid         aname      bid         bname      cid         cname      date                    num
----------- ---------- ----------- ---------- ----------- ---------- ----------------------- -----------
1           中国         21          辽宁         342         大连         2011-04-11 00:00:00.000 3
1           中国         11          北京         100         北京         2011-01-09 00:00:00.000 1
1           中国         28          河北         466         唐山         2011-08-01 00:00:00.000 2(3 行受影响)*/

Answer 5

create table #tb1
(
aid int,
aname nvarchar(10),
bid int,
bname nvarchar(10),
cid int,
cname nvarchar(10),
date datetime
)insert into #tb1 select 1,N'中国',21,N'辽宁',342,N'大连','2011-04-11'
insert into #tb1 select 1,N'中国',21,N'辽宁',111,N'沈阳','2011-03-11'
insert into #tb1 select 1,N'中国',21,N'辽宁',121,N'鞍山','2010-09-09'
insert into #tb1 select 1,N'中国',11,N'北京',100,N'北京','2011-01-09'
insert into #tb1 select 1,N'中国',28,N'河北',456,N'石家庄','2010-09-01'
insert into #tb1 select 1,N'中国',28,N'河北',466,N'唐山','2011-08-01'
go
;with ss as(
select row_number()over(partition by bid,bname order by[date] desc)as rownum ,* from #tb1)
select aid,
 aname ,
bid,
bname ,
cid,
cname,
[date] from ss where rownum=1

Answer 6

select a.*,b.cid,b.cname  from
(select max(aid) as aid, max(aname) as aname,bid,bname,max(date) as date , count(1) as num
from tablename
goup by bid,bname) a inner join tablename  b
on a.aid=b.aid and a.bid=b.bid and datediff(dd,a.date,b.date)=0