create table News
(
newsID int primary key identity(1,1) NOT NULL, --新闻编号
sectionId int foreign key (sectionId) references section(sectionID) not null, --附属新闻板块
title varchar(50) not NULL, --新闻标题
author varchar(50) not null, --拍摄作者
[content] nvarchar(max) not null, --发表内容
keyWords varchar(100) not NULL, --新闻关键字
submitDate datetime default(getdate()) not null, --发布日期
passCheck smallint default(0), --是否通过审核
clickedTimes int default(0), --点击次数
picture varchar(100) --生成图片
)insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(1,'火箭四连败', '小猫', '正文四连败', 'nba 火箭',0,0, '图片1')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(2,'我在读书', '小明', '正文我在读书,别烦我!', '读书', 1,0, '图片2')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(3,'财经新闻', '洋洋', '正文财经股票大涨', '财经 股票', 0,0, '图片3')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(4,'我岛爱死了!', '石头', '中方对待钓鱼岛事件给日本施加严重压力', '姜瑜强调',1,0, '图片4')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(5,'南方枪击事件', '驴子', '正文枪击', '军事 枪击', 0,0, '图片5')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(6,'奥巴马演讲', '小狗', '正文奥巴马发表就职演说', '美国 奥巴马 大选', 1,0, '图片6')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(7,'钓鱼岛时间', '小猪', '中方对日方如此对待钓鱼岛时间赶到非常愤慨', '军事报道', 1,0, '图片6')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(5,'嫦娥二号对月球的又一次勘探', '小温', '为了对以后进军月球做好预先了解', '科技军事', 6,0, '图片8')insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(5,'火箭四连败', '小猫', '正文四连败', 'nba 火箭',0,0, '图片1')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(5,'我在读书', '小明', '正文我在读书,别烦我!', '读书', 1,0, '图片2')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(7,'财经新闻', '洋洋', '正文财经股票大涨', '财经 股票', 0,0, '图片3')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(3,'我岛爱死了!', '石头', '中方对待钓鱼岛事件给日本施加严重压力', '姜瑜强调',1,0, '图片4')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(5,'南方枪击事件', '驴子', '正文枪击', '军事 枪击', 0,0, '图片5')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(5,'奥巴马演讲', '小狗', '正文奥巴马发表就职演说', '美国 奥巴马 大选', 1,0, '图片6')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(7,'钓鱼岛时间', '小猪', '中方对日方如此对待钓鱼岛时间赶到非常愤慨', '军事报道', 1,0, '图片6')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(6,'嫦娥二号对月球的又一次勘探', '小温', '为了对以后进军月球做好预先了解', '科技军事', 6,0, '图片8')insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(7,'火箭四连败', '小猫', '正文四连败', 'nba 火箭',0,0, '图片1')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(4,'我在读书', '小明', '正文我在读书,别烦我!', '读书', 1,0, '图片2')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(3,'财经新闻', '洋洋', '正文财经股票大涨', '财经 股票', 0,0, '图片3')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(4,'我岛爱死了!', '石头', '中方对待钓鱼岛事件给日本施加严重压力', '姜瑜强调',1,0, '图片4')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(2,'南方枪击事件', '驴子', '正文枪击', '军事 枪击', 0,0, '图片5')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(2,'奥巴马演讲', '小狗', '正文奥巴马发表就职演说', '美国 奥巴马 大选', 1,0, '图片6')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(6,'钓鱼岛时间', '小猪', '中方对日方如此对待钓鱼岛时间赶到非常愤慨', '军事报道', 1,0, '图片6')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(1,'嫦娥二号对月球的又一次勘探', '小温', '为了对以后进军月球做好预先了解', '科技军事', 6,0, '图片8')
备注:我新闻发布时间是获取系统时间 ,我要做新闻发布网,现在要做头条新闻那块,怎么写呢?头条新闻获取的是最后发布的新闻?
go
(
newsID int primary key identity(1,1) NOT NULL, --新闻编号
sectionId int foreign key (sectionId) references section(sectionID) not null, --附属新闻板块
title varchar(50) not NULL, --新闻标题
author varchar(50) not null, --拍摄作者
[content] nvarchar(max) not null, --发表内容
keyWords varchar(100) not NULL, --新闻关键字
submitDate datetime default(getdate()) not null, --发布日期
passCheck smallint default(0), --是否通过审核
clickedTimes int default(0), --点击次数
picture varchar(100) --生成图片
)insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(1,'火箭四连败', '小猫', '正文四连败', 'nba 火箭',0,0, '图片1')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(2,'我在读书', '小明', '正文我在读书,别烦我!', '读书', 1,0, '图片2')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(3,'财经新闻', '洋洋', '正文财经股票大涨', '财经 股票', 0,0, '图片3')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(4,'我岛爱死了!', '石头', '中方对待钓鱼岛事件给日本施加严重压力', '姜瑜强调',1,0, '图片4')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(5,'南方枪击事件', '驴子', '正文枪击', '军事 枪击', 0,0, '图片5')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(6,'奥巴马演讲', '小狗', '正文奥巴马发表就职演说', '美国 奥巴马 大选', 1,0, '图片6')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(7,'钓鱼岛时间', '小猪', '中方对日方如此对待钓鱼岛时间赶到非常愤慨', '军事报道', 1,0, '图片6')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(5,'嫦娥二号对月球的又一次勘探', '小温', '为了对以后进军月球做好预先了解', '科技军事', 6,0, '图片8')insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(5,'火箭四连败', '小猫', '正文四连败', 'nba 火箭',0,0, '图片1')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(5,'我在读书', '小明', '正文我在读书,别烦我!', '读书', 1,0, '图片2')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(7,'财经新闻', '洋洋', '正文财经股票大涨', '财经 股票', 0,0, '图片3')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(3,'我岛爱死了!', '石头', '中方对待钓鱼岛事件给日本施加严重压力', '姜瑜强调',1,0, '图片4')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(5,'南方枪击事件', '驴子', '正文枪击', '军事 枪击', 0,0, '图片5')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(5,'奥巴马演讲', '小狗', '正文奥巴马发表就职演说', '美国 奥巴马 大选', 1,0, '图片6')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(7,'钓鱼岛时间', '小猪', '中方对日方如此对待钓鱼岛时间赶到非常愤慨', '军事报道', 1,0, '图片6')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(6,'嫦娥二号对月球的又一次勘探', '小温', '为了对以后进军月球做好预先了解', '科技军事', 6,0, '图片8')insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(7,'火箭四连败', '小猫', '正文四连败', 'nba 火箭',0,0, '图片1')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(4,'我在读书', '小明', '正文我在读书,别烦我!', '读书', 1,0, '图片2')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(3,'财经新闻', '洋洋', '正文财经股票大涨', '财经 股票', 0,0, '图片3')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(4,'我岛爱死了!', '石头', '中方对待钓鱼岛事件给日本施加严重压力', '姜瑜强调',1,0, '图片4')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(2,'南方枪击事件', '驴子', '正文枪击', '军事 枪击', 0,0, '图片5')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(2,'奥巴马演讲', '小狗', '正文奥巴马发表就职演说', '美国 奥巴马 大选', 1,0, '图片6')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(6,'钓鱼岛时间', '小猪', '中方对日方如此对待钓鱼岛时间赶到非常愤慨', '军事报道', 1,0, '图片6')
insert into News(sectionId,title,author,[content],keyWords,passCheck,clickedTimes,picture) values(1,'嫦娥二号对月球的又一次勘探', '小温', '为了对以后进军月球做好预先了解', '科技军事', 6,0, '图片8')
备注:我新闻发布时间是获取系统时间 ,我要做新闻发布网,现在要做头条新闻那块,怎么写呢?头条新闻获取的是最后发布的新闻?
go
--头条新闻
select top 1 * from news order by submitDate desc--每个新闻板块的头条新闻select t.* from news t where submitDate = (select max(submitDate) from news where sectionId = t.sectionId) order by t.submitDate descselect t.* from news t where (select 1 from news where sectionId = t.sectionId and submitDate > t.submitDate) order by t.submitDate desc
--头条新闻
select top 1 * from news order by newsID desc--每个新闻板块的头条新闻select t.* from news t where newsID = (select max(newsID) from news where sectionId = t.sectionId) order by t.newsID descselect t.* from news t where (select 1 from news where sectionId = t.sectionId and newsID > t.newsID) order by t.newsID desc
--头条新闻create PROC GetHeadlines
(
@sectionId int
)as
select * from News where newsID=(select top 1 newsID from News where sectionId=@sectionId order by submitDate desc) order by sectionId执行查处的时间是:系统时间都是: 2010-10-23 15:41:37.920
那对于说要头条新闻就没有意义了。发布时间要改么?
那对于说要头条新闻就没有意义了。发布时间要改么? 这个和你的需求没有联系,不是很明白你的意思.或者你限制条件:submitDate < getdate()