比如
0736 New Moon Books Boston MA USA
0877 Binnet & Hardley Washington DC USA
1389 Algodata Infosystems Berkeley CA USA
1622 Five Lakes Publishing Chicago IL USA
1756 Ramona Publishers Dallas TX USA
9901 GGG&G M黱chen NULL Germany
9952 Scootney Books New York NY USA
9999 Lucerne Publishing Paris NULL France
我的输入条件是1662,那么要返回1389,1622,1759三条记录的New Moon Books字段,用游标怎么做?不用游标又怎么做??
什么样的解决方案最好?
0736 New Moon Books Boston MA USA
0877 Binnet & Hardley Washington DC USA
1389 Algodata Infosystems Berkeley CA USA
1622 Five Lakes Publishing Chicago IL USA
1756 Ramona Publishers Dallas TX USA
9901 GGG&G M黱chen NULL Germany
9952 Scootney Books New York NY USA
9999 Lucerne Publishing Paris NULL France
我的输入条件是1662,那么要返回1389,1622,1759三条记录的New Moon Books字段,用游标怎么做?不用游标又怎么做??
什么样的解决方案最好?
declare @OID int
select identity(int,1,1) as OID,* into # from 表
select @OID=OID from # where ID=1622
select * from # where OID in (@OID-1,@OID,@OID+1)
drop table #
union all
select * from table where myid =(select max(myid) from table where MYID < 1622 )
union all
select * from table where myid =1622
---------------------------------------------------------------------------------
对不起,不是New Moon Books字段
我用的是系统自带的pubs数据库中的publishers表,那个是pub_name字段
insert into tb select '0736','New Moon Books','Boston','MA','USA'
union all select '0877' ,'Binnet & Hardley' ,'Washington' ,'DC' ,'USA'
union all select '1389' ,'Algodata Infosystems' ,'Berkeley' ,'CA' ,'USA'
union all select '1622' ,'Five Lakes Publishing','Chicago' ,'IL' ,'USA'
union all select '1756' ,'Ramona Publishers' ,'Dallas' ,'TX' ,'USA'
union all select '9901' ,'GGG&G M黱','chen' ,NULL ,'Germany'
union all select '9952' ,'Scootney Books' ,'New York' ,'NY' ,'USA'
union all select '9999' ,'Lucerne Publishing' ,'Paris', NULL ,'France'
gocreate proc [select]
@id varchar(10)
asselect a.a from tb a,(select max(id) as id from tb where id<@id
union all
select min(id) as id from tb where id>@id)b where a.id=b.id
goexec [select] '1622'drop proc [select]
drop table tb
insert into tb select '0736','New Moon Books','Boston','MA','USA'
union all select '0877' ,'Binnet & Hardley' ,'Washington' ,'DC' ,'USA'
union all select '1389' ,'Algodata Infosystems' ,'Berkeley' ,'CA' ,'USA'
union all select '1622' ,'Five Lakes Publishing','Chicago' ,'IL' ,'USA'
union all select '1756' ,'Ramona Publishers' ,'Dallas' ,'TX' ,'USA'
union all select '9901' ,'GGG&G M黱','chen' ,NULL ,'Germany'
union all select '9952' ,'Scootney Books' ,'New York' ,'NY' ,'USA'
union all select '9999' ,'Lucerne Publishing' ,'Paris', NULL ,'France'
gocreate proc [select]
@id varchar(10)
asselect a.a from tb a,(select max(id) as id from tb where id<@id
union all
select min(id) as id from tb where id>@id
union all
select id from tb where id=@id)b where a.id=b.id
goexec [select] '1622'drop proc [select]
drop table tb
可不可以通过游标来实现呢??
将两个或更多查询的结果组合为单个结果集,该结果集包含联合查询中的所有查询的全部行。这与使用联接组合两个表中的列不同。使用 UNION 组合两个查询的结果集的两个基本规则是: 所有查询中的列数和列的顺序必须相同。
数据类型必须兼容。
语法
{ < query specification > | ( < query expression > ) }
UNION [ ALL ]
< query specification | ( < query expression > )
[ UNION [ ALL ] < query specification | ( < query expression > )
[ ...n ] ] 参数
< query_specification > | ( < query_expression > )是查询规范或查询表达式,用以返回与另一个查询规范或查询表达式所返回的数据组合的数据。作为 UNION 运算一部分的列定义可以不相同,但它们必须通过隐性转换实现兼容。下表说明数据类型与相应 (ith) 列的选项的比较规则。ith 列的数据类型 结果表中 ith 列的数据类型
非数据兼容类型(数据转换不由 Microsoft® SQL Server™ 隐性处理)。 SQL Server 返回的错误。
长度为 L1 和 L2 的固定长度 char。 长度与 L1 和 L2 中较长的那个相等的固定长度 char。
长度为 L1 和 L2 的固定长度 binary。 长度与 L1 和 L2 中较长的那个相等的固定长度 binary。
任一列或两列都是可变长度 char。 长度与为 ith 列指定的最大长度相等的可变长度 char。
任一列或两列都是可变长度 binary。 长度与为 ith 列指定的最大长度相等的可变长度 binary。
两列都是数字数据类型(如 smallint、int、float、money)。 与两列的最大精度相等的数据类型。例如,如果表 A 的 ith 列是 int 类型,表 B 的 ith 列是 float 类型,则 ith 列在结果表中的数据类型是 float,因为 float 比 int 更精确。
两列的描述都指定 NOT NULL。 指定 NOT NULL。
UNION指定组合多个结果集并将其作为单个结果集返回。ALL在结果中包含所有的行,包括重复行。如果没有指定,则删除重复行。
您这个是不是也不能支持非顺序的id啊??
create table tb(id varchar(10),a varchar(30),b varchar(20),c varchar(10),d varchar(20))
insert into tb select '0736','New Moon Books','Boston','MA','USA'
union all select '0877' ,'Binnet & Hardley' ,'Washington' ,'DC' ,'USA'
union all select '1389' ,'Algodata Infosystems' ,'Berkeley' ,'CA' ,'USA'
union all select '1756' ,'Ramona Publishers' ,'Dallas' ,'TX' ,'USA'
union all select '9901' ,'GGG&G M黱','chen' ,NULL ,'Germany'
union all select '9952' ,'Scootney Books' ,'New York' ,'NY' ,'USA'
union all select '9999' ,'Lucerne Publishing' ,'Paris', NULL ,'France'
union all select '1622' ,'Five Lakes Publishing','Chicago' ,'IL' ,'USA'
gocreate proc [select]
@id varchar(10)
asselect a.a from tb a,(select max(id) as id from tb where id<@id
union all
select min(id) as id from tb where id>@id
union all
select id from tb where id=@id)b where a.id=b.id
goexec [select] '1622'drop proc [select]
drop table tb
我哭啦~~
我要的是自然排序,因为这个只是个实例,真正用的时候我是分页存的,每次不可能先对整个的表先排序在找出来,我要的是给出的自然排序然后找出上其自身的上一条下一条
您给出的
--把数据调换一下位置
create table tb(id varchar(10),a varchar(30),b varchar(20),c varchar(10),d varchar(20))
insert into tb select '0736','New Moon Books','Boston','MA','USA'
union all select '0877' ,'Binnet & Hardley' ,'Washington' ,'DC' ,'USA'
union all select '1389' ,'Algodata Infosystems' ,'Berkeley' ,'CA' ,'USA'
union all select '1756' ,'Ramona Publishers' ,'Dallas' ,'TX' ,'USA'
union all select '9901' ,'GGG&G M黱','chen' ,NULL ,'Germany'
union all select '9952' ,'Scootney Books' ,'New York' ,'NY' ,'USA'
union all select '9999' ,'Lucerne Publishing' ,'Paris', NULL ,'France'
union all select '1622' ,'Five Lakes Publishing','Chicago' ,'IL' ,'USA'
go
这个表最后找出的应该是
9999 Lucerne Publishing'
1622 ,'Five Lakes Publishing'
NULL
thx
参见联机帮助
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'lake_list' AND type = 'P')
DROP PROCEDURE lake_list
GO
CREATE PROCEDURE lake_list
( @region varchar(30),
@size integer,
@lake_list_cursor CURSOR VARYING OUTPUT )
AS
BEGIN
DECLARE @ok SMALLINT
EXECUTE check_authority @region, username, @ok OUTPUT
IF @ok = 1
BEGIN
SET @lake_list_cursor =CURSOR LOCAL SCROLL FOR
SELECT name, lat, long, size, boat_launch, cost
FROM lake_inventory
WHERE locale = @region AND area >= @size
ORDER BY name
OPEN @lake_list_cursor
END
END
DECLARE @my_lakes_cursor CURSOR
DECLARE @my_region char(30)
SET @my_region = 'Northern Ontario'
EXECUTE lake_list @my_region, 500, @my_lakes_cursor OUTPUT
IF Cursor_Status('variable', '@my_lakes_cursor') <= 0
BEGIN
/* Some code to tell the user that there is no list of
lakes for him/her */
END
ELSE
BEGIN
FETCH @my_lakes_cursor INTO -- Destination here
-- Continue with other code here.
END
CREATE FUNCTION GetPubName (@ParaOID char(4))
RETURNS Char(120)
AS
BEGIN
DECLARE
@OID CHAR(4),
@pub_name1 CHAR(40),
@pub_name2 CHAR(180),
@pub_name3 CHAR(180)
Declare Test_Cursor Cursor for
select pub_id,pub_name from publishers for read only
Open Test_Cursor Fetch Test_Cursor into @OID,@pub_name1
while (@@Fetch_Status=0)
begin
if @OID=@ParaOID
begin
set @pub_name2=rtrim(@pub_name2)+'\'+rtrim(@pub_name1)
Fetch Next from Test_Cursor into @OID,@pub_name1
set @pub_name3=rtrim(@pub_name2)+'\'+rtrim(@pub_name1)
end
else
set @pub_name2=rtrim(@pub_name1)
Fetch Test_Cursor into @OID,@pub_name1
end
Close Test_Cursor
Deallocate Test_Cursor
Return(@pub_name3)
END
然后你通过该函数来调用
没分您也能回答,您真是太好了,我先看看,如果正确我再给您分,谢啦~~~
http://community.csdn.net/Expert/topic/4712/4712496.xml?temp=.2463037
帖子接分,再麻烦您帮我看看我的理解是否正确,谢谢您啦~~