PKID strName
1 Li
2 Wu
3 Hao
4 Jia
5 Wei
查询后的结果只有一条记录
strName
LiWuHao
7 个解决方案
#1
declare @s varchar(8000)
set @s=''
select top 3 @s=@s+strName from 表
print @s
set @s=''
select top 3 @s=@s+strName from 表
print @s
#2
declare @s varchar(8000)
set @s=''
select top 3 @s=@s+strName from 表
select strName=@s
set @s=''
select top 3 @s=@s+strName from 表
select strName=@s
#3
--或者是直接查询出来
select top 1 strName
+isnull((select top 1 strName from 表 where PKID<>a.PKID),'')
+isnull((select top 1 strName from 表 where PKID not in(select top 2 PKID from 表)),'')
from 表 a
select top 1 strName
+isnull((select top 1 strName from 表 where PKID<>a.PKID),'')
+isnull((select top 1 strName from 表 where PKID not in(select top 2 PKID from 表)),'')
from 表 a
#4
--示例
--示例数据
declare @t table(PKID int,strName varchar(10))
insert @t select 1,'Li'
union all select 2,'Wu'
union all select 3,'Hao'
union all select 4,'Jia'
union all select 5,'Wei'
--查询
select top 1 strName=strName
+isnull((select top 1 strName from @t where PKID<>a.PKID),'')
+isnull((select top 1 strName from @t where PKID not in(select top 2 PKID from @t)),'')
from @t a
/*--测试结果
strName
------------------------------
LiWuHao
(所影响的行数为 1 行)
--*/
--示例数据
declare @t table(PKID int,strName varchar(10))
insert @t select 1,'Li'
union all select 2,'Wu'
union all select 3,'Hao'
union all select 4,'Jia'
union all select 5,'Wei'
--查询
select top 1 strName=strName
+isnull((select top 1 strName from @t where PKID<>a.PKID),'')
+isnull((select top 1 strName from @t where PKID not in(select top 2 PKID from @t)),'')
from @t a
/*--测试结果
strName
------------------------------
LiWuHao
(所影响的行数为 1 行)
--*/
#5
邹建老大,我不是在存储过程中用呀,直接用SQl语句没法写吗?
#6
后面的那个就是直接的SQL语句
#7
谢了
#1
declare @s varchar(8000)
set @s=''
select top 3 @s=@s+strName from 表
print @s
set @s=''
select top 3 @s=@s+strName from 表
print @s
#2
declare @s varchar(8000)
set @s=''
select top 3 @s=@s+strName from 表
select strName=@s
set @s=''
select top 3 @s=@s+strName from 表
select strName=@s
#3
--或者是直接查询出来
select top 1 strName
+isnull((select top 1 strName from 表 where PKID<>a.PKID),'')
+isnull((select top 1 strName from 表 where PKID not in(select top 2 PKID from 表)),'')
from 表 a
select top 1 strName
+isnull((select top 1 strName from 表 where PKID<>a.PKID),'')
+isnull((select top 1 strName from 表 where PKID not in(select top 2 PKID from 表)),'')
from 表 a
#4
--示例
--示例数据
declare @t table(PKID int,strName varchar(10))
insert @t select 1,'Li'
union all select 2,'Wu'
union all select 3,'Hao'
union all select 4,'Jia'
union all select 5,'Wei'
--查询
select top 1 strName=strName
+isnull((select top 1 strName from @t where PKID<>a.PKID),'')
+isnull((select top 1 strName from @t where PKID not in(select top 2 PKID from @t)),'')
from @t a
/*--测试结果
strName
------------------------------
LiWuHao
(所影响的行数为 1 行)
--*/
--示例数据
declare @t table(PKID int,strName varchar(10))
insert @t select 1,'Li'
union all select 2,'Wu'
union all select 3,'Hao'
union all select 4,'Jia'
union all select 5,'Wei'
--查询
select top 1 strName=strName
+isnull((select top 1 strName from @t where PKID<>a.PKID),'')
+isnull((select top 1 strName from @t where PKID not in(select top 2 PKID from @t)),'')
from @t a
/*--测试结果
strName
------------------------------
LiWuHao
(所影响的行数为 1 行)
--*/
#5
邹建老大,我不是在存储过程中用呀,直接用SQl语句没法写吗?
#6
后面的那个就是直接的SQL语句
#7
谢了