要不聚集函数 ,比如在列上加max
要不在groupby后加上title
CREATE PROCEDURE Xl_Article_pro_getprenext
@articleId int
AS
select isnull(max(articleid),(select max(articleid) from Xl_Article)) as articleid , max(title) title from Xl_Article where articleid <@articleId
group by title
union all
select isnull(min(articleid),(select min(articleid) from Xl_Article)) as articleid ,max(title) title from Xl_Article where articleid>@articleId
group by title
要不在groupby后加上title
CREATE PROCEDURE Xl_Article_pro_getprenext
@articleId int
AS
select isnull(max(articleid),(select max(articleid) from Xl_Article)) as articleid , max(title) title from Xl_Article where articleid <@articleId
group by title
union all
select isnull(min(articleid),(select min(articleid) from Xl_Article)) as articleid ,max(title) title from Xl_Article where articleid>@articleId
group by title
group by articleid
or
select isnull(max(articleid),(select max(articleid) from Xl_Article)) as articleid , title from Xl_Article where articleid <@articleId
group by articleid,title
@articleId int
AS
select isnull(max(articleid),(select max(articleid) from Xl_Article)) as articleid , max(title) title from Xl_Article where articleid <@articleId
group by articleid
union all
select isnull(min(articleid),(select min(articleid) from Xl_Article)) as articleid ,max(title) title from Xl_Article where articleid>@articleId
group by articleid
GO
exec Xl_Article_pro_getprenext '23'
articleid title
----------- --------------------------------------------------
4 吃遍京城 个性餐厅大搜索
5 寻找隆福寺当年味道
9 吃遍京城 个性餐厅大搜索
10 寻找隆福寺当年味道
结果不对啊
@articleId int
AS
select articleid,title
from(
select isnull(max(articleid),(select max(articleid) from Xl_Article)) as articleid from Xl_Article where articleid <@articleId
group by articleid ) a
union all
select articleid,title
from(select isnull(min(articleid),(select min(articleid) from Xl_Article)) as articleid from Xl_Article where articleid>@articleId
group by articleid ) b
GO
CREATE PROCEDURE Xl_Article_pro_getprenext
@articleId int
AS
select articleid,title
from Xl_Article
where articleid =
(
select isnull(max(articleid),(select max(articleid) from Xl_Article)) as articleid from Xl_Article where articleid <@articleId
group by articleid ) union all
select articleid,title
from from Xl_Article
where articleid =(select isnull(min(articleid),(select min(articleid) from Xl_Article)) as articleid from Xl_Article where articleid>@articleId
group by articleid )
GO
@articleId int
AS
select isnull(max(articleid),(select max(articleid) from Xl_Article)) as articleid , max(title) title from Xl_Article where articleid <@articleId
group by title
union all
select isnull(min(articleid),(select min(articleid) from Xl_Article)) as articleid ,max(title) title from Xl_Article where articleid>@articleId
group by title
GO
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
@articleId int
AS
declare @a1 int
declare @a2 int
select @a1=isnull(max(articleid),(select max(articleid) from Xl_Article)) from Xl_Article where articleid <@articleId
select @a2=isnull(min(articleid),(select max(articleid) from Xl_Article)) from Xl_Article where articleid >@articleId
select articleid ,title from Xl_Article where articleid=@a1 or articleid=@a2
go
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。