取最前N条记录

最新推荐文章于 2025-04-17 17:58:48 发布

issacp

最新推荐文章于 2025-04-17 17:58:48 发布

阅读量416

点赞数

文章标签：测试体育 insert table object null

if object_id('t_news') is not null
drop table t_news

create table t_news
(
newsid             int                identity(1,1)          not null,
newstitle          varchar(100),
classid             int,
newsdate          datetime,
newskey             varchar(20),
newsource          varchar(100),
newscontent       ntext,
userid             int,
hits                int,
constraint PK_T_NEWS primary key (newsid)
);

insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('济南新闻测试1', 1,'2008-03-21 15:52:53', '测试', '齐鲁在线', '这是一条测试济南新闻1', 1, 3);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('济南新闻测试2', 1,'2008-03-23 15:52:53', '测试', '齐鲁在线', '这是一条测试济南新闻2', 1, 2);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('济南新闻测试3', 1,'2008-03-23 15:51:53','测试', '齐鲁在线', '这是一条测试济南新闻3', 1, 2);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('济南新闻测试4', 1,'2008-05-22 15:52:53','测试', '齐鲁在线', '这是一条测试济南新闻4', 1, 3);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('济南新闻测试5', 1,'2008-05-21 11:52:53', '测试', '齐鲁在线', '这是一条测试济南新闻5', 1, 5);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('济南新闻测试6', 1,'2008-05-21 12:52:53', '测试', '齐鲁在线', '这是一条测试济南新闻6', 1, 6);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('济南新闻测试7', 1,'2008-05-23 11:52:53', '测试', '齐鲁在线', '这是一条测试济南新闻7', 1, 9);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('济南新闻测试8', 1,'2008-05-25 11:52:53', '测试', '齐鲁在线', '这是一条测试济南新闻8', 1, 3);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('济南新闻测试9', 1,'2008-05-25 12:52:53', '测试', '齐鲁在线', '这是一条测试济南新闻9', 1, 12);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('济南新闻测试10', 1,'2008-07-25 13:52:53', '测试', '齐鲁在线', '这是一条测试济南新闻10', 1, 1);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('体育新闻测试1', 2,'2008-07-25 14:52:53', '测试', '新浪体育', '这是一条测试体育新闻1', 1, 5);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('体育新闻测试2', 2,'2008-07-25 15:52:53', '测试', '新浪体育', '这是一条测试体育新闻2', 1, 9);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('体育新闻测试3', 2,'2008-07-25 16:52:53', '测试', '新浪体育', '这是一条测试体育新闻3', 1, 6);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('体育新闻测试4', 2,'2008-7-25 17:52:53', '测试', '新浪体育', '这是一条测试体育新闻4', 1, 11);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('体育新闻测试5', 2,'2008-7-25 11:52:53', '测试', '新浪体育', '这是一条测试体育新闻5', 1, 2);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('体育新闻测试6', 2,'2008-7-25 12:52:53', '测试', '新浪体育', '这是一条测试体育新闻6', 1, 15);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('体育新闻测试7', 2,'2008-7-25 13:52:53', '测试', '新浪体育', '这是一条测试体育新闻7', 1, 23);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('体育新闻测试8', 2,'2008-7-25 14:52:53', '测试', '新浪体育', '这是一条测试体育新闻8', 1, 55);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('体育新闻测试9', 2,'2008-7-25 15:52:53', '测试', '新浪体育', '这是一条测试体育新闻9', 1, 11);
insert into t_news (newstitle, classid, newsdate, newskey, newsource, newscontent, userid, hits) values ('体育新闻测试10', 2,'2008-7-25 16:52:53', '测试', '新浪体育', '这是一条测试体育新闻10', 1, 10);

SELECT * FROM t_news a WHERE hits IN
(SELECT TOP 1 hits FROM t_news WHERE datediff(month,newsdate,a.newsdate)=0 ORDER BY hits desc)
ORDER BY a.newsdate desc

drop table t_news

18 体育新闻测试8 2 2008-07-25 14:52:53.000 测试新浪体育这是一条测试体育新闻8 1 55
  9 济南新闻测试9 1 2008-05-25 12:52:53.000 测试齐鲁在线这是一条测试济南新闻9 1 12
  1 济南新闻测试1 1 2008-03-21 15:52:53.000 测试齐鲁在线这是一条测试济南新闻1