select * from ( select row_number()over(partition by 类型 order by 时间 desc)rn,* from 新闻表 ) t where rn<=10
IF object_id('tb') IS NOT NULL DROP TABLE TB CREATE TABLE TB(id INT IDENTITY(1,1) PRIMARY KEY,item CHAR(1),times DATETIME, news NVARCHAR(50)) INSERT INTO dbo.TB SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'GFDSGH' UNION ALL SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'GRJEKGHY' UNION ALL SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'HYRTDJIOVFC' UNION ALL SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'34R54FDSA' UNION ALL SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'BVGFXBNH' UNION ALL SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'CVXZFGR' --创建测试数据SELECT id , item , times , news FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY item ORDER BY times DESC ) AS rowid , * FROM dbo.TB ) a WHERE rowid <= 2--前几条这里就写几
select * from (select *,ROW_NUMBER() over(partition by 新闻类型 order by getdate())as rowid from 表名) a where rowid<=10
借肉肉数据一下哇哈哈! 换个其他的方法IF object_id('tb') IS NOT NULL DROP TABLE TB CREATE TABLE TB(id INT IDENTITY(1,1) PRIMARY KEY,item CHAR(1),times DATETIME, news NVARCHAR(50)) INSERT INTO dbo.TB SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'GFDSGH' UNION ALL SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'GRJEKGHY' UNION ALL SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'HYRTDJIOVFC' UNION ALL SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'34R54FDSA' UNION ALL SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'BVGFXBNH' UNION ALL SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'CVXZFGR' --创建测试数据;with t as ( select type = 1 ,* from tb where id = 1 union all select type = (case when tb.item = t.item then t.type + 1 else 1 end),tb.* from tb inner join t on tb.id = t.id+1 ) select * from t where type <= 2 order by item--------------------------------------- type id item times news ----------- ----------- ---- ----------------------- -------------------------------------------------- 1 1 A 2011-04-01 00:00:00.000 GFDSGH 2 2 A 2011-03-27 00:00:00.000 GRJEKGHY 1 4 B 2011-03-01 00:00:00.000 34R54FDSA 2 5 B 2011-11-13 00:00:00.000 BVGFXBNH(4 行受影响)
呵呵表数据必选先根据item排序再做!
;with cte as( select rid=row_number() over (partition by 新闻类型 order by 时间 desc)rn,* from tb ) select * from cte where rid<=n
if object_id('tb','U') is not null drop table tb go create table tb ( id int identity(1,1), title varchar(10), type varchar(10) ) go insert into tb (title,type) select '新闻1','1' union all select '新闻2','1' union all select '新闻3','1' union all select '新闻4','1' union all select '新闻5','1' union all select '新闻6','1' union all select '新闻7','1' union all select '新闻8','1' union all select '新闻9','1' union all select '新闻10','1' union all select '新闻11','2' union all select '新闻12','2' union all select '新闻13','2' union all select '新闻14','2' union all select '新闻15','2' go ;with t(id,title,type,gprow) AS ( select *,gprow=(select count(1) from tb where type=a.type and id<=a.id) from tb a ) select * from t where gprow between 1 and 5 /*查询结果 id title type gprow ----------- ---------- ---------- ----------- 1 新闻1 1 1 2 新闻2 1 2 3 新闻3 1 3 4 新闻4 1 4 5 新闻5 1 5 11 新闻11 2 1 12 新闻12 2 2 13 新闻13 2 3 14 新闻14 2 4 15 新闻15 2 5(10 行受影响)*/
---怎么没人用这个? select distinct b.* from tb a cross apply (select top 10 * from tb where 类型=a.类型 order by 时间 desc)b
--------------------------------- -- Author: liangCK 小梁 -- Title : 查每个分组前N条记录 -- Date : 2008-11-13 17:19:23 -----------------------------------> 生成测试数据: #T IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T CREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME) INSERT INTO #T SELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALL SELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALL SELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALL SELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALL SELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALL SELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALL SELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALL SELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALL SELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALL SELECT '010',3,'黄占涛','SQL技术手册','2006-01-01'--SQL查询如下:--按GID分组,查每个分组中Date最新的前2条记录 --1.字段ID唯一时: SELECT * FROM #T AS T WHERE ID IN(SELECT TOP 2 ID FROM #T WHERE GID=T.GID ORDER BY Date DESC)--2.如果ID不唯一时: SELECT * FROM #T AS T WHERE 2>(SELECT COUNT(*) FROM #T WHERE GID=T.GID AND Date>T.Date)--SQL Server 2005 使用新方法--3.使用ROW_NUMBER()进行排位分组 SELECT ID,GID,Author,Title,Date FROM ( SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),* FROM #T ) AS T WHERE rid<=2--4.使用APPLY SELECT DISTINCT b.* FROM #T AS a CROSS APPLY ( SELECT TOP(2) * FROM #T WHERE a.GID=GID ORDER BY Date DESC ) AS b --结果 /*ID GID Author Title Date ---- ----------- ----------------------------- --------------------------------------- ----------------------- 003 1 格罗夫Groff.J.R. SQL完全手册 2009-07-01 00:00:00.000 004 1 KalenDelaney SQLServer2005技术内幕存储引擎 2008-08-01 00:00:00.000 005 2 Alex.Kriegel.Boris.M.Trukhnov SQL宝典 2007-10-05 00:00:00.000 007 2 胡百敬 SQLServer2005数据库开发详解 2008-06-15 00:00:00.000 009 3 赵松涛 SQLServer2005系统管理实录 2008-10-01 00:00:00.000 010 3 黄占涛 SQL技术手册 2006-01-01 00:00:00.000(6 行受影响) */--得到每组前几条数据 --假設每組Col1中, Col3不會重復--建立測試環境 Create Table TEST (Col1 Varchar(10), Col2 Varchar(10), Col3 Int) --插入數據 Insert TEST Select 'BD1V','Label', 4 Union All Select 'BD1V', 'BATT', 2 Union All Select 'BD1V', 'ODD', 3 Union All Select 'BD1V', 'HDD', 5 Union All Select 'BD1V', 'LCD', 1 Union All Select 'BD1W','HDD', 3 Union All Select 'BD1W','RAM', 8 Union All Select 'BD1W','TP CABLE', 5 Union All Select 'BD1W','LCD', 6 Union All Select 'BD1W','Label', 2 Union All Select 'BL3', 'LCD CABLE', 7 Union All Select 'BL3', 'LABEL', 6 Union All Select 'BL3', 'LCD', 5 Union All Select 'BL3', 'RAM', 1 Union All Select 'BL3D', 'Label', 4 GO --測試 --方法一: Select Col1, Col2, Col3 From TEST A Where (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3) < 3 Order By Col1, Col3 Desc --方法二: Select Col1, Col2, Col3 From TEST A Where Exists (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3 Having Count(*) < 3) Order By Col1, Col3 Desc --方法三: Select Col1, Col2, Col3 From TEST A Where Col3 In (Select TOP 3 Col3 From TEST Where Col1 = A.Col1 Order By Col3 Desc) Order By Col1, Col3 Desc GO --刪除測試環境 Drop Table TEST --結果 /* Col1 Col2 Col3 BD1V HDD 5 BD1V Label 4 BD1V ODD 3 BD1W RAM 8 BD1W LCD 6 BD1W TP CABLE 5 BL3 LCD CABLE 7 BL3 LABEL 6 BL3 LCD 5 BL3D Label 4 */
select * from (
select row_number()over(partition by 类型 order by 时间 desc)rn,* from 新闻表
) t where rn<=10
DROP TABLE TB
CREATE TABLE TB(id INT IDENTITY(1,1) PRIMARY KEY,item CHAR(1),times DATETIME, news NVARCHAR(50))
INSERT INTO dbo.TB
SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'GFDSGH' UNION ALL
SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'GRJEKGHY' UNION ALL
SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'HYRTDJIOVFC' UNION ALL
SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'34R54FDSA' UNION ALL
SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'BVGFXBNH' UNION ALL
SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'CVXZFGR'
--创建测试数据SELECT id , item , times , news
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY item ORDER BY times DESC ) AS rowid ,
* FROM dbo.TB ) a
WHERE rowid <= 2--前几条这里就写几
换个其他的方法IF object_id('tb') IS NOT NULL
DROP TABLE TB
CREATE TABLE TB(id INT IDENTITY(1,1) PRIMARY KEY,item CHAR(1),times DATETIME, news NVARCHAR(50))
INSERT INTO dbo.TB
SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'GFDSGH' UNION ALL
SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'GRJEKGHY' UNION ALL
SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'HYRTDJIOVFC' UNION ALL
SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'34R54FDSA' UNION ALL
SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'BVGFXBNH' UNION ALL
SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'CVXZFGR'
--创建测试数据;with t as
(
select type = 1 ,* from tb where id = 1
union all
select type = (case when tb.item = t.item then t.type + 1 else 1 end),tb.*
from tb inner join t on tb.id = t.id+1
)
select * from t where type <= 2
order by item---------------------------------------
type id item times news
----------- ----------- ---- ----------------------- --------------------------------------------------
1 1 A 2011-04-01 00:00:00.000 GFDSGH
2 2 A 2011-03-27 00:00:00.000 GRJEKGHY
1 4 B 2011-03-01 00:00:00.000 34R54FDSA
2 5 B 2011-11-13 00:00:00.000 BVGFXBNH(4 行受影响)
;with cte as(
select rid=row_number() over (partition by 新闻类型 order by 时间 desc)rn,*
from tb
)
select * from cte where rid<=n
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
id int identity(1,1),
title varchar(10),
type varchar(10)
)
go
insert into tb (title,type)
select '新闻1','1' union all
select '新闻2','1' union all
select '新闻3','1' union all
select '新闻4','1' union all
select '新闻5','1' union all
select '新闻6','1' union all
select '新闻7','1' union all
select '新闻8','1' union all
select '新闻9','1' union all
select '新闻10','1' union all
select '新闻11','2' union all
select '新闻12','2' union all
select '新闻13','2' union all
select '新闻14','2' union all
select '新闻15','2'
go
;with t(id,title,type,gprow) AS
(
select *,gprow=(select count(1) from tb where type=a.type and id<=a.id) from tb a
)
select * from t where gprow between 1 and 5
/*查询结果
id title type gprow
----------- ---------- ---------- -----------
1 新闻1 1 1
2 新闻2 1 2
3 新闻3 1 3
4 新闻4 1 4
5 新闻5 1 5
11 新闻11 2 1
12 新闻12 2 2
13 新闻13 2 3
14 新闻14 2 4
15 新闻15 2 5(10 行受影响)*/
select
distinct b.*
from
tb a
cross apply
(select top 10 * from tb where 类型=a.类型 order by 时间 desc)b
-- Author: liangCK 小梁
-- Title : 查每个分组前N条记录
-- Date : 2008-11-13 17:19:23
-----------------------------------> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)
INSERT INTO #T
SELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALL
SELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALL
SELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALL
SELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALL
SELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALL
SELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALL
SELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALL
SELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALL
SELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALL
SELECT '010',3,'黄占涛','SQL技术手册','2006-01-01'--SQL查询如下:--按GID分组,查每个分组中Date最新的前2条记录
--1.字段ID唯一时:
SELECT * FROM #T AS T WHERE ID IN(SELECT TOP 2 ID FROM #T WHERE GID=T.GID ORDER BY Date DESC)--2.如果ID不唯一时:
SELECT * FROM #T AS T WHERE 2>(SELECT COUNT(*) FROM #T WHERE GID=T.GID AND Date>T.Date)--SQL Server 2005 使用新方法--3.使用ROW_NUMBER()进行排位分组
SELECT ID,GID,Author,Title,Date
FROM
(
SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),*
FROM #T
) AS T
WHERE rid<=2--4.使用APPLY
SELECT DISTINCT b.*
FROM #T AS a
CROSS APPLY
(
SELECT TOP(2) * FROM #T WHERE a.GID=GID ORDER BY Date DESC
) AS b
--结果
/*ID GID Author Title Date
---- ----------- ----------------------------- --------------------------------------- -----------------------
003 1 格罗夫Groff.J.R. SQL完全手册 2009-07-01 00:00:00.000
004 1 KalenDelaney SQLServer2005技术内幕存储引擎 2008-08-01 00:00:00.000
005 2 Alex.Kriegel.Boris.M.Trukhnov SQL宝典 2007-10-05 00:00:00.000
007 2 胡百敬 SQLServer2005数据库开发详解 2008-06-15 00:00:00.000
009 3 赵松涛 SQLServer2005系统管理实录 2008-10-01 00:00:00.000
010 3 黄占涛 SQL技术手册 2006-01-01 00:00:00.000(6 行受影响)
*/--得到每组前几条数据
--假設每組Col1中, Col3不會重復--建立測試環境
Create Table TEST
(Col1 Varchar(10),
Col2 Varchar(10),
Col3 Int)
--插入數據
Insert TEST Select 'BD1V','Label', 4
Union All Select 'BD1V', 'BATT', 2
Union All Select 'BD1V', 'ODD', 3
Union All Select 'BD1V', 'HDD', 5
Union All Select 'BD1V', 'LCD', 1
Union All Select 'BD1W','HDD', 3
Union All Select 'BD1W','RAM', 8
Union All Select 'BD1W','TP CABLE', 5
Union All Select 'BD1W','LCD', 6
Union All Select 'BD1W','Label', 2
Union All Select 'BL3', 'LCD CABLE', 7
Union All Select 'BL3', 'LABEL', 6
Union All Select 'BL3', 'LCD', 5
Union All Select 'BL3', 'RAM', 1
Union All Select 'BL3D', 'Label', 4
GO
--測試
--方法一:
Select Col1, Col2, Col3 From TEST A
Where (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3) < 3
Order By Col1, Col3 Desc
--方法二:
Select Col1, Col2, Col3 From TEST A
Where Exists (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3 Having Count(*) < 3)
Order By Col1, Col3 Desc
--方法三:
Select Col1, Col2, Col3 From TEST A
Where Col3 In (Select TOP 3 Col3 From TEST Where Col1 = A.Col1 Order By Col3 Desc)
Order By Col1, Col3 Desc
GO
--刪除測試環境
Drop Table TEST
--結果
/*
Col1 Col2 Col3
BD1V HDD 5
BD1V Label 4
BD1V ODD 3
BD1W RAM 8
BD1W LCD 6
BD1W TP CABLE 5
BL3 LCD CABLE 7
BL3 LABEL 6
BL3 LCD 5
BL3D Label 4
*/