SQL 存储过程 NOT IN 的问题。

[nikolas]

ALTER     PROCEDURE sArticle
  @currentPage int 
AS
declare @sql1 varchar(250)
declare @sql2 varchar(250) 

set @sql1 ='select top 20 ID,Topic,Author,AddTime,ViewNum from Article order by ID desc'
set @sql2 ='select top 20 ID,Topic,Author,AddTime,ViewNum from Article where ID not in (select top (@currentPage-1)*20 ID from Article order by ID desc) order by ID desc'
if (@currentPage=1) 
  execute (@sql1)
else
  execute (@sql2)

Not IN (1,2,3,4 ...) 上面红色的语句，怎样写才是正确的？

[nikolas]

select top 20 ID,Topic,Author,AddTime,ViewNum from Article where ID not in (select top (@currentPage-1)*20 ID from Article order by ID desc) order by ID desc

上面是这样的。

[wzy_love_sly]

ALTER     PROCEDURE sArticle
  @currentPage int 
AS
declare @sql1 nvarchar(250)
declare @sql2 nvarchar(250) 

set @sql1 ='select top 20 ID,Topic,Author,AddTime,ViewNum from Article order by ID desc'
set @sql2 ='select top 20 ID,Topic,Author,AddTime,ViewNum from Article where ID not in (select top (@currentPage-1)*20 ID from Article order by ID desc) order by ID desc'
if (@currentPage=1) 
  execute (@sql1)
else
 exec sp_executesql @sql2,N'@currentPage int',@currentPage

nvarchar动态

[nikolas]

谢谢wzy_love_sly
但是 在查询分析器里执行：当@currentPage=2时
就提示：过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型。

[wzy_love_sly]

把 @sql的类型改nvarchar(),上面说了也写了

[nikolas]

改了
输入2后 提示：第 1 行: '(' 附近有语法错误。

[nikolas]

主要是NOT IN 后面括号里的语句出了问题，第一条语句没有问题的。

[nikolas]

not in (' + select top (@currentPage-1)*20 ID from Article order by ID desc + ') order by ID

这样也不行。

[wzy_love_sly]

(@currentpate-1*20)扩号,还有错就帖你语句

[huangjh_love]

TOP 后不能跟一个变量。

[nikolas]

这样显然不行，2-20 ？
错误还是一样的。语句就是上面你写的语句 ，。

[nikolas]

select top 20 ID,Topic,Author,AddTime,ViewNum from Article where ID not in (select top 20 ID from Article order by ID desc) order by ID desc

这样就没问题！

[wzy_love_sly]

你是2000还是05？加（）就行了吧

ALTER     PROCEDURE sArticle
  @currentPage int 
AS
declare @sql1 nvarchar(250)
declare @sql2 nvarchar(250) 

set @sql1 ='select top 20 ID,Topic,Author,AddTime,ViewNum from Article order by ID desc'
set @sql2 ='select top 20 ID,Topic,Author,AddTime,ViewNum from Article where ID not in (select top ((@currentPage-1)*20) ID from Article order by ID desc) order by ID desc'
if (@currentPage=1) 
  execute (@sql1)
else
 exec sp_executesql @sql2,N'@currentPage int',@currentPage

[nikolas]

sql 2000

加括号也没用的，错误还是一样！

[wzy_love_sly]

create table tb(id int,name varchar(20))
insert into tb select 1,'a'
insert into tb select 2,'b'
insert into tb select 3,'c'
insert into tb select 4,'d'
insert into tb select 5,'e'
insert into tb select 6,'f'
insert into tb select 7,'g'
insert into tb select 8,'h'
insert into tb select 9,'h'
insert into tb select 10,'h'

declare @sql nvarchar(250)
declare @currentPage int
set @currentPage=3
set @sql ='select top 5 * from tb where id not in (select top ((@currentPage-1)*2) id from tb order by id desc) order by id desc'
 exec sp_executesql @sql,N'@currentPage int',@currentPage

--不在 （3-1）*2 =4（7，8，9，10）的id 倒序的前5个

id	name
6	f
5	e
4	d
3	c
2	b

05可以象上面，2000不能加变量

[nikolas]

那怎么办啊？能改成其他的吗？

[nikolas]

CREATE  PROCEDURE sArticle
  @currentPage int 
AS
declare @sql1 varchar(250)
declare @sql2 varchar(250) 
declare @sPage varchar(250)
set @sPage = convert(varchar(250),(@currentPage-1)*20)

set @sql1 ='select top 20 ID,Topic,Author,AddTime,ViewNum from Article order by ID desc'
set @sql2 ='select top 20 ID,Topic,Author,AddTime,ViewNum from Article where ID not in (select top ' + @sPage + '  ID from SundyArticle order by ID desc) order by ID desc'
if (@currentPage=1) 
  execute (@sql1)
else
 execute (@sql2)

由“TOP 后不能跟一个变量。“ 才想到了，改成上面的，正常了。

谢谢 huangjh_love 无情浪子

[yimeng123]

重薪诚聘C++
http://topic.csdn.net/u/20080227/14/8bce0844-bd15-42f0-9cda-a343d5d6601b.html