下面的数据我想得到 11372、11358、11367、11371 最新(createTime)的四条数据(编号可随意指定):
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11373 墙纸美女
11358 04北京车展车模
11373 墙纸美女
11373 墙纸美女
11358 04北京车展车模
11358 04北京车展车模
11367 8届深圳车展美女
11367 8届深圳车展美女
11367 8届深圳车展美女
11367 8届深圳车展美女
11367 8届深圳车展美女
11371 汽车美女Ⅰ
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11373 墙纸美女
11377 深圳车展靓车美女
11358 04北京车展车模
11358 04北京车展车模
11358 04北京车展车模
11358 04北京车展车模
11358 04北京车展车模
11367 8届深圳车展美女
11367 8届深圳车展美女
11367 8届深圳车展美女
11367 8届深圳车展美女
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11373 汽车美女Ⅳ
11373 汽车美女Ⅳ
11373 汽车美女Ⅳ
11378 汽车美女Ⅳ呵呵,不要给我4个 select ...
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11373 墙纸美女
11358 04北京车展车模
11373 墙纸美女
11373 墙纸美女
11358 04北京车展车模
11358 04北京车展车模
11367 8届深圳车展美女
11367 8届深圳车展美女
11367 8届深圳车展美女
11367 8届深圳车展美女
11367 8届深圳车展美女
11371 汽车美女Ⅰ
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11373 墙纸美女
11377 深圳车展靓车美女
11358 04北京车展车模
11358 04北京车展车模
11358 04北京车展车模
11358 04北京车展车模
11358 04北京车展车模
11367 8届深圳车展美女
11367 8届深圳车展美女
11367 8届深圳车展美女
11367 8届深圳车展美女
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11373 汽车美女Ⅳ
11373 汽车美女Ⅳ
11373 汽车美女Ⅳ
11378 汽车美女Ⅳ呵呵,不要给我4个 select ...
解决方案 »
- 请教SQL链接服务器访问oracle数据库中含有blog字段的视图的解决方案
- 求一简单视图?
- 【不让帖子沉】无法连接远程数据库:sqlserver不存在或访问被拒绝
- [新手]求一存储过程
- sql这段代码为什么出错
- telnet 127.0.0.1 1433 异常,命令提示符又回到原来的状态
- 关于表中字段的格式问题
- win7下mysql-connector-odbc-3.51.12-win32 安装后找不到安装目录 怎么解决
- 关于自建数据库访问的问题,求各位赐教了,不然睡不着~(大力~~大力~~~)
- 请问大家那有关于asp,xml+sqlserver的论文我想参考一下,谢谢
- 请问子节点合计的语句应该怎么写呢?
- 如何恢复数据库数据
from tb t
where (select count(*) from tb where id=t.id and createTime>t.createTime)<4
from tb t
where createTime in
(select top 4 createTime
from tb
where id=t.id order by createTime desc )
from tb t
where createTime in(select top 4 createTime from tb where id=t.id order by createTime desc)
FROM
(
SELECT *, rows_number(partition by ID order by creat_time desc) as row_id
) tb
WHERE row_id=1
if OBJECT_ID('tb') is not null drop table tb
create table tb
(
id int,
name VARCHAR(100),
createDate Datetime
)
goINSERT INTO tb
SELECT 1,'aaa','2009-01-01'
union ALL
SELECT 1,'aaa','2009-01-02'
union ALL
SELECT 1,'aaa','2009-01-03'
union ALL
SELECT 1,'aaa','2009-01-04'
union ALL
SELECT 1,'aaa','2009-01-05'
union ALL
SELECT 1,'aaa','2009-01-06'
union ALL
SELECT 1,'aaa','2009-01-07'
union ALL
SELECT 1,'aaa','2009-01-08'
union ALL
SELECT 2,'bbb','2009-01-01'
union ALL
SELECT 2,'bbb','2009-01-02'
union ALL
SELECT 2,'bbb','2009-01-03'
union ALL
SELECT 2,'bbb','2009-01-04'
union ALL
SELECT 2,'bbb','2009-01-05'
union ALL
SELECT 2,'bbb','2009-01-06'
union ALL
SELECT 2,'bbb','2009-01-07'
union ALL
SELECT 3,'bb','2009-01-08'SELECT *
FROM
(
SELECT *, row_number() over (partition by id order by createDate desc) as row_id from tb
) t
WHERE row_id=1
from tb t
where (select count(*)
from tb
where id=t.id and createTime>t.createTime)<4 --每组要返回多少自己定
and id in(11372,11358,11367,11371)
if OBJECT_ID('tb') is not null drop table tb
create table tb
(
id int,
name VARCHAR(100),
createDate Datetime
)
goINSERT INTO tb
SELECT 1,'aaa','2009-01-01'
union ALL
SELECT 1,'aaa','2009-01-02'
union ALL
SELECT 1,'aaa','2009-01-03'
union ALL
SELECT 1,'aaa','2009-01-04'
union ALL
SELECT 1,'aaa','2009-01-05'
union ALL
SELECT 1,'aaa','2009-01-06'
union ALL
SELECT 1,'aaa','2009-01-07'
union ALL
SELECT 1,'aaa','2009-01-08'
union ALL
SELECT 2,'bbb','2009-01-01'
union ALL
SELECT 2,'bbb','2009-01-02'
union ALL
SELECT 2,'bbb','2009-01-03'
union ALL
SELECT 2,'bbb','2009-01-04'
union ALL
SELECT 2,'bbb','2009-01-05'
union ALL
SELECT 2,'bbb','2009-01-06'
union ALL
SELECT 2,'bbb','2009-01-07'
union ALL
SELECT 3,'bb','2009-01-08'SELECT *
FROM
(
SELECT *, row_number() over (partition by id order by createDate desc) as row_id from tb
where id=1 or id=2 --这里指定ID呢.如:11372、11358、11367、11371
) t
WHERE row_id=1
select * from
(
select rn=row_number()over(partition by id order by createtime desc),*
from tb
where id in(11372,11358,11367,11371)
) t where rn<=4
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ
11372 汽车美女Ⅳ11358 04北京车展车模
11358 04北京车展车模
11358 04北京车展车模
11358 04北京车展车模11367 8届深圳车展美女
11367 8届深圳车展美女
11367 8届深圳车展美女
11367 8届深圳车展美女11371 墙纸美女
11371 04北京车展车模
11371 墙纸美女
11371 墙纸美女其他编号不管
from tb t
where (select count(*)
from tb
where id=t.id and id in(11372,11358,11367,11371) and createTime>t.createTime)<4 --每组要返回多少自己定
and id in(11372,11358,11367,11371)
drop table #tb
create table #tb (id int, context nvarchar(10), date_time datetime)
insert into #tb
select 11372,'汽车美女Ⅳ ','2010-05-14 01:20:30' union all
select 11372,'汽车美女Ⅳ ','2010-05-14 02:20:30' union all
select 11372,'汽车美女Ⅳ ','2010-05-14 03:20:30' union all
select 11372,'汽车美女Ⅳ ','2010-05-14 04:20:30' union all
select 11372,'汽车美女Ⅳ ','2010-05-14 05:20:30' union all
select 11373,'墙纸美女 ','2010-05-14 06:20:30' union all
select 11358,'04北京车展车模 ','2010-05-14 07:20:30' union all
select 11373,'墙纸美女 ','2010-05-14 08:20:30' union all
select 11373,'墙纸美女 ','2010-05-14 09:20:30' union all
select 11358,'04北京车展车模 ','2010-05-14 10:20:30' union all
select 11358,'04北京车展车模 ','2010-05-14 11:20:30' union all
select 11367,'8届深圳车展美女 ','2010-05-14 12:20:30' union all
select 11367,'8届深圳车展美女 ','2010-05-14 13:20:30' union all
select 11367,'8届深圳车展美女 ','2010-05-14 14:20:30' union all
select 11367,'8届深圳车展美女 ','2010-05-14 15:20:30' union all
select 11367,'8届深圳车展美女 ','2010-05-14 16:20:30' union all
select 11371,'汽车美女Ⅰ ','2010-05-14 17:20:30' union all
select 11372,'汽车美女Ⅳ ','2010-05-14 18:20:30' union all
select 11372,'汽车美女Ⅳ ','2010-05-14 19:20:30' union all
select 11372,'汽车美女Ⅳ ','2010-05-14 20:20:30' union all
select 11373,'墙纸美女 ','2010-05-14 21:20:30' union all
select 11377,'深圳车展靓车美女','2010-05-14 22:20:30' union all
select 11358,'04北京车展车模 ','2010-05-14 23:20:30' union all
select 11358,'04北京车展车模 ','2010-05-14 23:21:30' union all
select 11358,'04北京车展车模 ','2010-05-14 01:22:30' union all
select 11358,'04北京车展车模 ','2010-05-14 01:23:30' union all
select 11358,'04北京车展车模 ','2010-05-14 01:24:30' union all
select 11367,'8届深圳车展美女 ','2010-05-14 01:25:30' union all
select 11367,'8届深圳车展美女 ','2010-05-14 01:26:30' union all
select 11367,'8届深圳车展美女 ','2010-05-14 01:27:30' union all
select 11367,'8届深圳车展美女 ','2010-05-14 01:28:30' union all
select 11372,'汽车美女Ⅳ ','2010-05-14 01:29:30' union all
select 11372,'汽车美女Ⅳ ','2010-05-14 01:30:30' union all
select 11373,'汽车美女Ⅳ ','2010-05-14 01:40:30' union all
select 11373,'汽车美女Ⅳ ','2010-05-14 01:50:30' union all
select 11373,'汽车美女Ⅳ ','2010-05-14 01:20:31' union all
select 11378,'汽车美女Ⅳ ','2010-05-14 01:20:32' select *
from #tb a
where date_time in (select top 4 date_time from #tb where id = a.id order by date_time desc)
and id in (11372,11358,11367,11371)
order by id--结果
/*
11358 04北京车展车模 2010-05-14 10:20:30.000
11358 04北京车展车模 2010-05-14 11:20:30.000
11358 04北京车展车模 2010-05-14 23:20:30.000
11358 04北京车展车模 2010-05-14 23:21:30.000
11367 8届深圳车展美女 2010-05-14 13:20:30.000
11367 8届深圳车展美女 2010-05-14 14:20:30.000
11367 8届深圳车展美女 2010-05-14 15:20:30.000
11367 8届深圳车展美女 2010-05-14 16:20:30.000
11371 汽车美女Ⅰ 2010-05-14 17:20:30.000
11372 汽车美女Ⅳ 2010-05-14 18:20:30.000
11372 汽车美女Ⅳ 2010-05-14 19:20:30.000
11372 汽车美女Ⅳ 2010-05-14 20:20:30.000
11372 汽车美女Ⅳ 2010-05-14 05:20:30.000
*/