table a
NO time
1390000001 2009-01-01
1390000002 2009-02-01
1390000001 2010-01-01
1390000003 2009-01-01
1390000001 2012-03-01table B
NO
1300000001
1300000002
1300000004
table A 有大量冗余数据数据量庞大2000万, B 500条左右
需要统计B 最近5次时间记录SQL查询语句 group /top
NO time
1390000001 2009-01-01
1390000002 2009-02-01
1390000001 2010-01-01
1390000003 2009-01-01
1390000001 2012-03-01table B
NO
1300000001
1300000002
1300000004
table A 有大量冗余数据数据量庞大2000万, B 500条左右
需要统计B 最近5次时间记录SQL查询语句 group /top
distinct a.*
from
a
cross apply
(select top 5 * from b where NO=a.no order by time desc)b写个他们不经常写的
a.*
from
(select id=row_number()over(partition by no order by time desc),* from a)a
inner join
b
on
a.no=b.no
where
a.id<=5
(
select * from a join b on a.NO=b.NO
)
select * from (
select row_number()over(order by time desc)num,*from cte)A
where num between 1 and 5
select a.no,
a.time
from b
inner join
(
select no,time,
row_number() over(partiton by no order by time desc) rownum
from a
)a
on b.no = a.no
and a.rownum <=5
(
select * from a join b on a.NO=b.NO
)
select * from (
select row_number()over(partition by NO order by time desc)num,*from cte)A
where num between 1 and 5
table A 有大量冗余数据数据量庞大2000万, B 500条左右
需要统计B 最近5次时间记录select distinct t.* from b
cross apply(select top 5 * from a where no=b.no order by [time] desc) t
服务器: 消息 195,级别 15,状态 10,行 1
'row_number' 不是可以识别的 函数名。
第 1 行: 'apply' 附近有语法错误。我是2000
from tableB b
inner join tableA a on b.NO=a.NO
where (select count(1) from tableA c
where c.NO=a.NO and datediff(d,c.time,a.time)>0)<=5
对了,你的数据量挺大的,建索引了不,要是不建索引,估计会非常慢的。建个索引试试看:create index idx_a_xxx on a(no,time desc)