if exists (select 1 from sys.tables where name = 'messages')
drop table messages
go
create table messages
(
Id int identity(1,1), --ID
UserId int not null, --用户ID
MesSortId int not null, --留言类型
MessageTitle varchar(100) not null, --留言标题
MessageContent varchar(max) not null, --留言正文
MessageTime datetime not null, --留言时间
AdminId int, --管理员ID
MessageBack varchar(max), --回复内容
MessageBackTime datetime, --回复时间
IsView int default(0), --是否显示
constraint PK_messages_Id primary key (Id), --主键约束
constraint IX_messages_MessageTitle unique nonclustered(MessageTitle) --唯一约束
)
godeclare @userId int,@mesSortId int,@messageTitle varchar(100),@messageContent varchar(max),@messageTime datetime
declare @adminId int,@messageBack varchar(max),@messageBackTime datetime,@isView int
declare @i int
set @i = 1
while @i<10000
begin
set @userId = 1
set @mesSortId = ((@i+4)%4)+1
set @messageTitle = '测试留言标题'+cast(@i as varchar(10))
set @messageContent = '测试留言内容'+cast(@i as varchar(10))
set @messageTime = getdate()
insert into messages values(@userId,@mesSortId,@messageTitle,@messageContent,@messageTime,@adminId,@messageBack,@messageBackTime,@isView)
set @i = @i+1
end
select * from messagesdeclare @rows int,@page int,@key varchar(50),@sql varchar(200)
set @rows = 30
set @page = 1
set @key = '测试'
set @sql ='Select top '+cast(@rows as varchar(10))+' * from messages where '+
'(Id not in '+
'(Select top (('+cast(@page as varchar(10))+'-1)*'+cast(@rows as varchar(10))+') Id from messages where (MessageTitle like %'+@key+'% or MessageContent like %'+@key+'%) order by Id desc)'+
') '+
'and (MessageTitle like %'+@key+'% or MessageContent like %'+@key+'% ) order by Id desc'
exec(@sql)
print(@sql)报错:消息 102,级别 15,状态 1,第 1 行
'测试' 附近有语法错误。
解决方案 »
- SQL死锁问题:Transaction (Process ID 65) was deadlocked on lock resources with...
- 求以下语句怎么写
- ??sql语句中的不等于某一个值,但是null值也被排除了??
- 急啊!如何过滤掉一张表中某些个字段中重复的数据??
- 【急求】一个SQL语句能取出记录吗?详见内
- SQL问题.在线等....
- 关于sqlserver临时表的问题,请教高手!
- 求组,新手SQL求改进,有问题的地方都标了,求高手赐教
- SQL怎么实现逐行查询?
- oracle初学者的问题,轻松拿到30分
- group by 问题
- 想修改表里的一个字段的默认值,应该怎么写语句啊?高手请指教
运行下不就知道了?..LIKE '%测试%'
set @rows = 30
set @page = 1
set @key = '测试'
set @sql ='Select top '+cast(@rows as varchar(10))+' * from messages where '+
'(Id not in '+
'(Select top (('+cast(@page as varchar(10))+'-1)*'+cast(@rows as varchar(10))+') Id from messages where (MessageTitle like %'+@key+'% or MessageContent like %'+@key+'%) order by Id desc)'+
') '+
'and (MessageTitle like ''%'+@key+'%'' or MessageContent like ''%'+@key+'%'' ) order by Id desc'
exec(@sql)
print(@sql)