id portname
1 HongKong
2 高雄
3 他科马
4 奥克兰
5 檀香山
6 关岛
.. ..用一条SQL语句实现这样的结果:
EX HongKong TO 关岛(高雄,他科马,奥克兰,关岛)
1 HongKong
2 高雄
3 他科马
4 奥克兰
5 檀香山
6 关岛
.. ..用一条SQL语句实现这样的结果:
EX HongKong TO 关岛(高雄,他科马,奥克兰,关岛)
调试欢乐多
1 HongKong
2 高雄
3 他科马
4 奥克兰
5 檀香山
6 关岛
.. ..用一条SQL语句实现这样的结果:
EX HongKong TO 关岛(高雄,他科马,奥克兰,关岛)关岛两次,檀香山没了,没看懂
id portname
1 HongKong
2 高雄
3 他科马
4 奥克兰
5 檀香山
6 关岛
.. ..用一条SQL语句实现这样的结果:
EX HongKong TO 关岛(高雄,他科马,奥克兰,檀香山,关岛)
(
@namefrom varchar(20),
@nameto varcahr(20)
)
as
declare @idfrom int,@idto int
declare @str varchar(8000)
declare @sql varchar(8000)
select @str = '',@sql = ''
select @idfrom = id from tb1 where portname = @namefrom
select @idto = id from tb1 where portname = @nameto
select @str = @str + ',' + portname from tb1 where id between @idfrom and @idto
set @str = stuff(@str,1,1,'')
set @str = suff(@str,1,charindex(',',@str),'')
set @sql = @sql+ 'select EX '+ @namefrom + 'TO' + @nameto + '= '''+ @str+''''
exec(@sql)
insert into tb values(1, 'HongKong')
insert into tb values(2, '高雄')
insert into tb values(3, '他科马')
insert into tb values(4, '奥克兰')
insert into tb values(5, '檀香山')
insert into tb values(6, '关岛')
go
declare @min as varchar(10)
select @min = portname from tb where id in (select min(id) from tb)
declare @max as varchar(10)
select @max = portname from tb where id in (select max(id) from tb)declare @name varchar(100)
set @name=''
select @name=@name+ portname + ',' from tb order by id
set @name = 'EX ' + @min + ' TO ' + @max + '(' + substring(replace(@name , @min ,''),2,len(replace(@name , @min ,''))-2) + ')'
print @namedrop table tb/*
EX HongKong TO 关岛(高雄,他科马,奥克兰,檀香山,关岛)
*/