有一张简单的表:表名 :UserMap字段 Id(主键) FromUserId(字符串) ToUserId(字符串)
1 007 008
2 008 009
3 009 010
... ... ...意思是说 007映射成008, 008映射成009,009映射成010
要求输入 007 直接换算成 010
这样的Sql语句怎么写,谢谢!
1 007 008
2 008 009
3 009 010
... ... ...意思是说 007映射成008, 008映射成009,009映射成010
要求输入 007 直接换算成 010
这样的Sql语句怎么写,谢谢!
--> 测试数据:[UserMap]
if object_id('[UserMap]') is not null drop table [UserMap]
go
create table [UserMap]([Id] int,[FromUserId] varchar(3),[ToUserId] varchar(3))
insert [UserMap]
select 1,'007','008' union all
select 2,'008','009' union all
select 3,'009','010'
declare @FromUserId varchar(10)=''
set @FromUserId='007';with cte as (
select FromUserId,ToUserId from UserMap where [FromUserId]=@FromUserId
union all
select u.FromUserId,u.ToUserId from cte l
join UserMap u on l.ToUserId =u.FromUserId
)
select top 1 ToUserId from cte order by FromUserId desc/*ToUserId
--------
010(1 行受影响)*/
drop table [UserMap]
insert [tb]
select 1,'007','008' union all
select 2,'008','009' union all
select 3,'009','010' union alldeclare @st varchar(10)
set @st='007'
select @st= case when t.fromuserid=@st then touserid else @st end from tb t
select @st
IF OBJECT_ID('UserMap','u')IS NOT NULL
DROP TABLE UserMap
CREATE TABLE UserMap(
ID INT IDENTITY(1,1) PRIMARY KEY
,FromUserId NVARCHAR(50)
,ToUserId NVARCHAR(50)
)INSERT INTO UserMap(FromUserId,ToUserId)
SELECT N'007',N'008'
UNION ALL SELECT N'008',N'009'
UNION ALL SELECT N'009',N'010'
GO-- 自定义存储过程名字
IF OBJECT_ID('UserDefinition_sp','p') IS NOT NULL
DROP PROCEDURE UserDefinition_sp
GO
CREATE PROCEDURE UserDefinition_sp(
@FromUserId NVARCHAR(50)
)
AS-- 递归
;WITH cte AS(
SELECT ID,FromUserId,ToUserId FROM UserMap
WHERE FromUserId=@FromUserId
UNION ALL
SELECT o.ID,o.FromUserId,o.ToUserId FROM UserMap AS o
INNER JOIN cte AS x ON o.FromUserId=x.ToUserId
)
SELECT ID,FromUserId,ToUserId FROM cteGo
EXEC UserDefinition_sp @FromUserId=N'007'
DROP PROCEDURE UserDefinition_sp
DROP TABLE UserMap