UserID VersionID Subject Date Medium ID
2 1921208 东风悦达起亚狮跑SUV 2009-03-19 15:59:16.703 报纸 3
2 1921208 东风悦达起亚狮跑SUV 2009-03-19 16:54:01.813 报纸 4
2 1920970 广州本田思迪轿车 2009-03-19 16:56:57.660 报纸 5
2 1921003 哈飞汽车 2009-03-19 16:57:01.973 报纸 6
2 1920313 创鑫地板 2009-03-19 16:57:05.490 电视 7
2 1920313 创鑫地板 2009-03-19 16:57:30.193 电视 8想从该表中按日期排序查出subject不重复的前五项,谢谢!
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE subject=A.subject AND [Date] > A.[Date])
ORDER BY [Date] DESC;
order by date desc
SELECT * FROM TB T WHERE ID IN(SELECT TOP 5 ID FROM TB WHERE Subject=T.Subject ORDER BY Date DESC)
order by date desc
from tb t
where not exists(select 1 from tb where subject=t.subject and date<t.date)
order by date --如果要查最新的5项,这里加个desc
INSERT @TB
SELECT '2', '1921208', N'东风悦达起亚狮跑SUV', '2009-03-19 15:59:16.703', N'报纸', 3 UNION ALL
SELECT '2', '1921208', N'东风悦达起亚狮跑SUV', '2009-03-19 16:54:01.813', N'报纸', 4 UNION ALL
SELECT '2', '1920970', N'广州本田思迪轿车', '2009-03-19 16:56:57.660', N'报纸', 5 UNION ALL
SELECT '2', '1921003', N'哈飞汽车', '2009-03-19 16:57:01.973', N'报纸', 6 UNION ALL
SELECT '2', '1920313', N'创鑫地板', '2009-03-19 16:57:05.490', N'电视', 7 UNION ALL
SELECT '2', '1920313', N'创鑫地板', '2009-03-19 16:57:30.193', N'电视', 8SELECT TOP 5 *
FROM @TB AS T
WHERE NOT EXISTS(SELECT * FROM @TB WHERE [Subject]=T.[Subject] AND [Date]<T.[Date])
ORDER BY [Date]
/*
UserID VersionID Subject Date Medium ID
------ --------- ----------- ----------------------- ------ -----------
2 1921208 东风悦达起亚狮跑SUV 2009-03-19 15:59:16.703 报纸 3
2 1920970 广州本田思迪轿车 2009-03-19 16:56:57.660 报纸 5
2 1921003 哈飞汽车 2009-03-19 16:57:01.973 报纸 6
2 1920313 创鑫地板 2009-03-19 16:57:05.490 电视 7
*/
from tb t
where not exists(select 1 from tb where subject=t.subject and date<t.date)
order by date
如原表是
1 a 2006-10-01
2 a 2006-10-02
3 b 2006-10-01
4 b 2006-10-02
5 c 2006-10-01
6 d 2006-10-01
7 e 2006-10-01
查出为1 a 2006-10-02
3 b 2006-10-02
5 c 2006-10-01
6 d 2006-10-01
7 e 2006-10-01
where not exists(select 1 from tb where subject=a.subject and date>a.date)
order by date desc
DECLARE @TB TABLE([COL1] INT, [COL2] VARCHAR(1), [COL3] DATETIME)
INSERT @TB
SELECT 1, 'a', '2006-10-01' UNION ALL
SELECT 2, 'a', '2006-10-02' UNION ALL
SELECT 3, 'b', '2006-10-01' UNION ALL
SELECT 4, 'b', '2006-10-02' UNION ALL
SELECT 5, 'c', '2006-10-01' UNION ALL
SELECT 6, 'd', '2006-10-01' UNION ALL
SELECT 7, 'e', '2006-10-01'SELECT *
FROM @TB AS T
WHERE NOT EXISTS(SELECT * FROM @TB WHERE [COL2]=T.[COL2] AND [COL3]<T.[COL3])
ORDER BY [COL3]
/*
COL1 COL2 COL3
----------- ---- -----------------------
1 a 2006-10-01 00:00:00.000
3 b 2006-10-01 00:00:00.000
5 c 2006-10-01 00:00:00.000
6 d 2006-10-01 00:00:00.000
7 e 2006-10-01 00:00:00.000
*/
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE subject=A.subject AND [Date] > A.[Date])
ORDER BY [Date] DESC
go
create table [tb]([id] int,[subject] varchar(1),[date] datetime)
insert [tb]
select 1,'a','2006-10-01' union all
select 2,'a','2006-10-02' union all
select 3,'b','2006-10-01' union all
select 4,'b','2006-10-02' union all
select 5,'c','2006-10-01' union all
select 6,'d','2006-10-01' union all
select 7,'e','2006-10-01'
go
--select * from [tb]select top 5 id=min(id),subject,date=max(date)
from tb
group by subject
order by date desc,id
/*
id subject date
----------- ------- -----------------------
1 a 2006-10-02 00:00:00.000
3 b 2006-10-02 00:00:00.000
5 c 2006-10-01 00:00:00.000
6 d 2006-10-01 00:00:00.000
7 e 2006-10-01 00:00:00.000(5 行受影响)
*/
INSERT #TT
SELECT '2', '1921208', N'东风悦达起亚狮跑SUV', '2009-03-19 15:59:16.703', N'报纸', 3 UNION ALL
SELECT '2', '1921208', N'东风悦达起亚狮跑SUV', '2009-03-19 16:54:01.813', N'报纸', 4 UNION ALL
SELECT '2', '1920970', N'广州本田思迪轿车', '2009-03-19 16:56:57.660', N'报纸', 5 UNION ALL
SELECT '2', '1921003', N'哈飞汽车', '2009-03-19 16:57:01.973', N'报纸', 6 UNION ALL
SELECT '2', '1920313', N'创鑫地板', '2009-03-19 16:57:05.490', N'电视', 7 UNION ALL
SELECT '2', '1920313', N'创鑫地板', '2009-03-19 16:57:30.193', N'电视', 8select top 5 * from
(
select *,row_number() over(partition by [Subject] order by [Date] desc) rank from #TT
) T
where rank=1 order by [Date] desc
UserID VersionID Subject Date Medium ID rank
------ --------- ----------- ----------------------- ------ ----------- --------------------
2 1920313 创鑫地板 2009-03-19 16:57:30.193 电视 8 1
2 1921003 哈飞汽车 2009-03-19 16:57:01.973 报纸 6 1
2 1920970 广州本田思迪轿车 2009-03-19 16:56:57.660 报纸 5 1
2 1921208 东风悦达起亚狮跑SUV 2009-03-19 16:54:01.813 报纸 4 1(4 行受影响)
declare @t table(userid int,VersionID int,Subject varchar(50),[date] datetime,Medium varchar(10),[ID] int)
insert @t
select 2,1921208,'东风悦达起亚狮跑SUV','2009-03-19 15:59:16.703','报纸',3 Union all
select 2,1921208,'东风悦达起亚狮跑SUV','2009-03-19 16:54:01.813','报纸',4 Union all
select 2,1920970,'广州本田思迪轿车','2009-03-19 16:56:57.660','报纸',5 Union all
select 2,1921003,'哈飞汽车','2009-03-19 16:57:01.973','报纸',6 Union all
select 2,1921208,'创鑫地板','2009-03-19 16:57:05.490','电视',7 Union all
select 2,1921208,'创鑫地板','2009-03-19 16:57:30.193','电视',8 union all
select 2,1921208,'东风雪铁龙','2009-03-19 16:59:30.193','电视',9
select top 5 userid,VersionID,a.Subject,a.[date],Medium,[ID] from @t a inner join
(select Subject,max(date) as [date] from @t group by Subject) b on a.Subject=b.Subject and a.date=b.date