情况是这个,用户分三级,代理商,经销商,普通用户代理商ID与经销商UPID关联经销商ID与普通用户UPID关联即如下:代理商表:ID Name
1001 代理商1
1003 代理商2经销商表ID Name UPID
2001 经销商1 1001
2002 经销商2 1002用户表ID Name UPID
3001 用户1 2001
3002 用户2 2002
3003 用户3 2001希望得到结果:
1、查询代理商ID时得到所有下级经销商及用户,当然也包含自身
1、查询经销商ID时得到所有下级用户,包含自身比如
查询代理商ID=1001得到如下结果ID Name
1001 代理商1
2001 经销商1
3001 用户1
3003 用户3查询经销商ID=2001得到如下结果ID Name
2001 经销商1
3001 用户1
3003 用户3最好不用存储过程,谢谢。
1001 代理商1
1003 代理商2经销商表ID Name UPID
2001 经销商1 1001
2002 经销商2 1002用户表ID Name UPID
3001 用户1 2001
3002 用户2 2002
3003 用户3 2001希望得到结果:
1、查询代理商ID时得到所有下级经销商及用户,当然也包含自身
1、查询经销商ID时得到所有下级用户,包含自身比如
查询代理商ID=1001得到如下结果ID Name
1001 代理商1
2001 经销商1
3001 用户1
3003 用户3查询经销商ID=2001得到如下结果ID Name
2001 经销商1
3001 用户1
3003 用户3最好不用存储过程,谢谢。
-----传入代理商ID
;WITH CTE
AS
(
SELECT J.ID,J.NAME FROM [#经销商表] J
WHERE J.UPID=1001 ---传入代理商ID
)
SELECT ID,NAME FROM #代理商 WHERE ID=1001 ---传入代理商ID
UNION ALL
SELECT * FROM CTE
UNION ALL
SELECT U.ID,U.NAME FROM [#用户表] U,CTE C WHERE UPID =C.ID
-----传入经销商ID
;WITH CTE
AS
(
SELECT J.ID,J.NAME FROM [#经销商表] J
WHERE J.id=2001 ---传入经销商ID
)
SELECT * FROM CTE
UNION ALL
SELECT U.ID,U.NAME FROM [#用户表] U,CTE C WHERE UPID =C.IDID NAME
----------- ----------------------------------
1001 代理商1
2001 经销商1
3001 用户1
3003 用户3(4 row(s) affected)ID NAME
----------- --------------------------
2001 经销商1
3001 用户1
3003 用户3(3 row(s) affected)
if object_id('[dlTb]') is not null drop table [dlTb]
go
create table [dlTb] (ID int,Name varchar(7))
insert into [dlTb]
select 1001,'代理商1' union all
select 1003,'代理商2'
--> 测试数据: [jxTb]
if object_id('[jxTb]') is not null drop table [jxTb]
go
create table [jxTb] (ID int,Name varchar(7),UPID int)
insert into [jxTb]
select 2001,'经销商1',1001 union all
select 2002,'经销商2',1002
--> 测试数据: [yhYh]
if object_id('[yhTB]') is not null drop table [yhTB]
go
create table [yhTB] (ID int,Name varchar(5),UPID int)
insert into [yhTB]
select 3001,'用户1',2001 union all
select 3002,'用户2',2002 union all
select 3003,'用户3',2001;WITH CTE AS (
SELECT id ,
Name ,
0 as Parent
from dlTb
union all
SELECT
A.id AS jxID,
A.Name AS jxName ,
A.UPID as jxParent
from [jxTB] a,dltb b where a.upid=b.id
union all
select
B.id AS YhID,
B.Name AS YhName,
B.UPID as yhParent
from [jxTB] a,[yhTB] B WHERE A.ID=B.UPID
)
,
CTE1 AS
(
SELECT * FROM CTE WHERE ID='2001'
UNION ALL
SELECT A.ID,A.NAME,A.PARENT FROM CTE A,CTE1 B WHERE A.PARENT=B.ID
) SELECT * FROM CTE1
id Name Parent
----------- ------- -----------
2001 经销商1 1001
3001 用户1 2001
3003 用户3 2001(3 行受影响)
declare @rs table (id char(4), name varchar(64))set @querycode = '1001'
insert into @rs select id, name from 代理商表 where id = @querycode
insert into @rs select id, name from 经销商表 where id = @querycode or upid in (select id from @rs)
insert into @rs select id, name from 用户表 where id = @querycode or upid in (select id from @rs)select * from @rs
SELECT id,Name,0 as Parent from dlTb
union all
SELECT id ,Name , UPID from [jxTB]
union all
select id, Name, UPID from [yhTB]
)
,
CTE1 AS
(
SELECT * FROM CTE WHERE ID='2001'
UNION ALL
SELECT A.ID,A.NAME,A.PARENT FROM CTE A,CTE1 B WHERE A.PARENT=B.ID
) SELECT * FROM CTE1