use AdventureWorksDECLARE @Pos int;
SET @Pos = '611';
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE AddressID BETWEEN @Pos - 400 -4 AND @Pos - 400 + 4
UNION
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE AddressID BETWEEN @Pos - 300 -4 AND @Pos - 300 + 4
UNION
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE AddressID BETWEEN @Pos - 200 -4 AND @Pos - 200 + 4
UNION
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE AddressID BETWEEN @Pos - 100 -4 AND @Pos - 100 + 4
UNION
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE AddressID BETWEEN @Pos -4 AND @Pos + 4
UNION
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE AddressID BETWEEN @Pos + 100 -4 AND @Pos + 100 + 4
UNION
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE AddressID BETWEEN @Pos + 200 -4 AND @Pos + 200 + 4
UNION
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE AddressID BETWEEN @Pos + 300 -4 AND @Pos + 300 + 4
UNION
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE AddressID BETWEEN @Pos + 400 -4 AND @Pos + 400 + 4;
解决方案 »
- SQl错误
- 小数点取整~
- left join查询的问题 意思很明确就是怎么查不了
- 如何处理我这个SQL语句问题,急!~~~在线等!~~~~~~~~~
- 表中一字段,值是日期型的,数据为:2006-4-28 19:36:21,我想查询日期为2006-4-28的数据,怎样才能把该行数据查出来?
- 这样一个sql语句该怎么写?
- 请问如何成批改变nvarchar类型中的某个字符
- 请问哪有比较SQL Server,Oracle,Sybase优缺点的简体中文的文章?
- 如何在OS启动后,没有SQL SERVER的那个托盘呀?
- 在数据库school中创建一个AFTER触发器trigest3,当在teacher表中删除记录时,给出被删除教师所上课的课程名,并撤销该删除操作。
- 100分求语法详解
- execute中的问题
declare @sql nvarchar(4000)
set @sql=''
set @sql='SELECT AddressID, AddressLine1 '
+' FROM Person.Address'
+' WHERE AddressID BETWEEN'
exec(@sql+@Pos + 400 -4 AND @Pos + 400 + 4;)--把条件限制好就更简单了
--try:SELECT AddressID, AddressLine1
FROM Person.Address
WHERE right(ltrim(AddressID),2) between 7 and 15
and left(ltrim(AddressID),len(ltrim(AddressID)-2) between 2 and 10
DECLARE @Pos int;
SET @Pos = '611';
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE addressid/10 - @pos/10 between -4 and 4
and addressid%10 - @pos%10 between -4 and 4
(
AddressID int,
AddressLine1 nvarchar(40)
)
DECLARE @Pos int;
SET @Pos = '611'declare @sql nvarchar(4000),
@n int
set @sql=''
set @sql='SELECT AddressID, AddressLine1 '
+' FROM Person.Address'
+' WHERE AddressID BETWEEN '
set @n=-5
while @n<4begin
set @n=@n+1
print (@sql+cast(@Pos as nvarchar(10)) + ' +100*'+cast(@n as nvarchar(10))+' -4 AND '+ cast(@Pos as nvarchar(10)) + ' +100*'+cast(@n as nvarchar(10)) + '+4')
--set @sql=(@sql+' union '+ (@sql+cast(@Pos as nvarchar(10)) + ' +100*'+cast(@n as nvarchar(10))+' -4 AND '+ cast(@Pos as nvarchar(10)) + ' +100*'+cast(@n as nvarchar(10)) + '+4'))
end
drop table Address
SET @Pos = '611';
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE (addressid - @pos)/100 between -4 and 4
and (addressid - @pos)%100 between -4 and 4
SET @Pos = '611';
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE (addressid - @pos between -404 and -396)
or(addressid - @pos between -304 and -296)
or(addressid - @pos between -204 and -196)
or(addressid - @pos between -104 and -96)
or(addressid - @pos between -4 and 4)
or(addressid - @pos between 96 and 104)
or(addressid - @pos between 196 and 204)
or(addressid - @pos between 296 and 304)
or(addressid - @pos between 396 and 404)
SET @Pos = '611';
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE (addressid - @pos between -404 and -396)
or((addressid - @pos)/100 between -3 and 3) and
(addressid - @pos)%100 between -4 and 4))
or(addressid - @pos between 396 and 404)
SET @Pos = '611';
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE ((addressid - @pos)%100) between 0 and 4
SET @Pos = '611';
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE (addressid+404 - @pos)/100 between 0 and 8
and(addressid+404 - @pos)%100 between 0 and 8
你们的方法都不错,学习了。
不过好像只有cson_cson的
6楼和9楼的两段代码有正确答案。
9楼更精炼。其他的要么有语法错误,要么结果集和想的不一样。每次问问题都能学不少东西。谢谢了各位。