我表中加的是NULL,你赋的是'',这是不一样的。我用的int,你用的varchar其实解决也很简单CREATE TABLE t1(编号 INT,名称 VARCHAR(10),组编号 VARCHAR(10),下属编号 VARCHAR(20)) INSERT t1 SELECT 1, 'XX单位', '' , '3,5' UNION ALL SELECT 3 , 'XX部门', 1 , '' UNION ALL SELECT 4 , 'XX部门', 7 , '' UNION ALL SELECT 5 , 'XX部门', 1 , ''CREATE TABLE t2(编号 INT,名称 VARCHAR(10),组编号 VARCHAR(10))INSERT t2 SELECT 1, 'aa', 3 UNION ALL SELECT 2 , 'bb' , 1 UNION ALL SELECT 3 , 'cc' , 2 UNION ALL SELECT 4 , 'dd' , 5 GOSELECT * FROM t1 SELECT * FROM t2 GO CREATE PROC p @aid INT AS BEGIN SELECT b.* FROM t2 b WHERE CHARINDEX ( ',' + RTRIM(组编号) + ',' , ','+( SELECT ISNULL(NULLIF(RTRIM(编号),'') + ',','') + 下属编号 FROM t1 a WHERE 编号= (SELECT 组编号 FROM t1 WHERE 编号=@aid) )+',' )>0
END GO EXEC p 3 /* 1 aa 3 2 bb 1 4 dd 5*/ GO DROP TABLE t1,t2 GO DROP PROC p GO
--> --> (Roy)生成測試數據
set nocount on; if not object_id('A') is null drop table A Go Create table A([编号] int,[名称] nvarchar(4),[组编号] int,[下属编号] nvarchar(3)) Insert A select 1,N'XX单位',null,N'3,5' union all select 3,N'XX部门',1,null union all select 4,N'XX部门',null,null union all select 5,N'XX部门',1,null Go --> --> (Roy)生成測試數據
set nocount on; if not object_id('B') is null drop table B Go Create table B([编号] int,[名称] nvarchar(2),[单位编号] int) Insert B select 1,N'aa',3 union all select 2,N'bb',1 union all select 3,N'cc',2 union all select 4,N'dd',5 union all select 5,N'ee',4 Go if object_id('p1') is not null drop proc p1 go create proc p1(@Code int ) as select * from B where [单位编号]=@Code or exists(select 1 from A t1 join A t2 on t1.[组编号]=t2.[编号] where t1.[编号]=@Code and (','+t2.[下属编号]+',' like '%'+rtrim(b.[单位编号])+'%' or t1.[组编号]=b.[单位编号])) go --测试 exec p1 3exec p1 4/* 编号 名称 单位编号 ----------- ---- ----------- 1 aa 3 2 bb 1 4 dd 5编号 名称 单位编号 ----------- ---- ----------- 5 ee 4*/
CREATE TABLE t1(编号 VARCHAR(10),名称 VARCHAR(10),组编号 VARCHAR(10),下属编号 VARCHAR(20)) INSERT t1 SELECT 1, 'XX单位', '' , '3,5' UNION ALL SELECT 3 , 'XX部门', 1 , '' UNION ALL SELECT 4 , 'XX部门', '' , '' UNION ALL SELECT 5 , 'XX部门', 1 , ''CREATE TABLE t2(编号 VARCHAR(10),名称 VARCHAR(10),组编号 VARCHAR(10))INSERT t2 SELECT 1, 'aa', 3 UNION ALL SELECT 2 , 'bb' , 1 UNION ALL SELECT 3 , 'cc' , 2 UNION ALL SELECT 4 , 'dd' , 5 UNION ALL SELECT 5 , 'ee' , 4 GOSELECT * FROM t1 SELECT * FROM t2 GO CREATE PROC p @aid INT AS BEGIN SELECT b.* FROM t2 b INNER JOIN (SELECT 编号,下属编号 FROM t1 a WHERE 编号= (SELECT CASE WHEN 组编号='' THEN 编号 ELSE 组编号 END 组编号 FROM t1 WHERE 编号=@aid) ) a ON CHARINDEX ( ',' + ( CASE WHEN a.编号='' THEN b.编号 ELSE b.组编号 END ) + ',' , ',' + a.编号 + ',' + a.下属编号 + ',' )>0
END GOGO EXEC p 3 /* 1 aa 3 2 bb 1 4 dd 5*/EXEC p 4 /* 5 ee 4*/ GO DROP TABLE t1,t2 GO DROP PROC p GO
set nocount on; if not object_id('A') is null drop table A Go Create table A([编号] int,[名称] nvarchar(4),[组编号] int,[下属编号] nvarchar(3)) Insert A select 1,N'XX单位',null,N'3,5' union all select 3,N'XX部门',1,null union all select 4,N'XX部门',null,null union all select 5,N'XX部门',1,null Go --> --> (Roy)生成測試數據
set nocount on; if not object_id('B') is null drop table B Go Create table B([编号] int,[名称] nvarchar(2),[单位编号] int) Insert B select 1,N'aa',3 union all select 2,N'bb',1 union all select 3,N'cc',2 union all select 4,N'dd',5 union all select 5,N'ee',4 Go if object_id('p1') is not null drop proc p1 go create proc p1(@Code int ) as select * from B where exists(select 1 from A where (b.[单位编号]=@Code or ','+[下属编号]+',' like '%,'+rtrim(b.[单位编号])+',%') and [编号]=@Code ) or exists(select 1 from A t1 join A t2 on t1.[组编号]=t2.[编号] where t1.[编号]=@Code and (','+t2.[下属编号]+',' like '%'+rtrim(b.[单位编号])+'%' or t1.[组编号]=b.[单位编号])) go --测试 exec p1 1 exec p1 3 exec p1 4 /* 编号 名称 单位编号 ----------- ---- ----------- 1 aa 3 2 bb 1 4 dd 5编号 名称 单位编号 ----------- ---- ----------- 1 aa 3 2 bb 1 4 dd 5编号 名称 单位编号 ----------- ---- ----------- 5 ee 4 */
EXEC p 1 /* 1 aa 3 2 bb 1 4 dd 5 */ EXEC p 3 /* 1 aa 3 2 bb 1 4 dd 5*/EXEC p 4 /* 5 ee 4*/
INSERT t1 SELECT 1, 'XX单位', '' , '3,5'
UNION ALL SELECT 3 , 'XX部门', 1 , ''
UNION ALL SELECT 4 , 'XX部门', 7 , ''
UNION ALL SELECT 5 , 'XX部门', 1 , ''CREATE TABLE t2(编号 INT,名称 VARCHAR(10),组编号 VARCHAR(10))INSERT t2 SELECT 1, 'aa', 3
UNION ALL SELECT 2 , 'bb' , 1
UNION ALL SELECT 3 , 'cc' , 2
UNION ALL SELECT 4 , 'dd' , 5 GOSELECT * FROM t1
SELECT * FROM t2
GO
CREATE PROC p
@aid INT
AS
BEGIN
SELECT b.* FROM t2 b
WHERE CHARINDEX
(
',' + RTRIM(组编号) + ','
,
','+(
SELECT ISNULL(NULLIF(RTRIM(编号),'') + ',','') + 下属编号 FROM t1 a
WHERE 编号=
(SELECT 组编号 FROM t1 WHERE 编号=@aid)
)+','
)>0
END
GO
EXEC p 3
/*
1 aa 3
2 bb 1
4 dd 5*/
GO
DROP TABLE t1,t2
GO
DROP PROC p
GO
set nocount on;
if not object_id('A') is null
drop table A
Go
Create table A([编号] int,[名称] nvarchar(4),[组编号] int,[下属编号] nvarchar(3))
Insert A
select 1,N'XX单位',null,N'3,5' union all
select 3,N'XX部门',1,null union all
select 4,N'XX部门',null,null union all
select 5,N'XX部门',1,null
Go
--> --> (Roy)生成測試數據
set nocount on;
if not object_id('B') is null
drop table B
Go
Create table B([编号] int,[名称] nvarchar(2),[单位编号] int)
Insert B
select 1,N'aa',3 union all
select 2,N'bb',1 union all
select 3,N'cc',2 union all
select 4,N'dd',5 union all
select 5,N'ee',4
Go
if object_id('p1') is not null
drop proc p1
go
create proc p1(@Code int )
as
select
*
from
B
where
[单位编号]=@Code
or
exists(select 1 from A t1 join A t2 on t1.[组编号]=t2.[编号] where t1.[编号]=@Code and (','+t2.[下属编号]+',' like '%'+rtrim(b.[单位编号])+'%' or t1.[组编号]=b.[单位编号]))
go
--测试
exec p1 3exec p1 4/*
编号 名称 单位编号
----------- ---- -----------
1 aa 3
2 bb 1
4 dd 5编号 名称 单位编号
----------- ---- -----------
5 ee 4*/
INSERT t1 SELECT 1, 'XX单位', '' , '3,5'
UNION ALL SELECT 3 , 'XX部门', 1 , ''
UNION ALL SELECT 4 , 'XX部门', '' , ''
UNION ALL SELECT 5 , 'XX部门', 1 , ''CREATE TABLE t2(编号 VARCHAR(10),名称 VARCHAR(10),组编号 VARCHAR(10))INSERT t2 SELECT 1, 'aa', 3
UNION ALL SELECT 2 , 'bb' , 1
UNION ALL SELECT 3 , 'cc' , 2
UNION ALL SELECT 4 , 'dd' , 5
UNION ALL SELECT 5 , 'ee' , 4 GOSELECT * FROM t1
SELECT * FROM t2
GO
CREATE PROC p
@aid INT
AS
BEGIN
SELECT b.* FROM t2 b
INNER JOIN
(SELECT 编号,下属编号 FROM t1 a
WHERE 编号=
(SELECT CASE WHEN 组编号='' THEN 编号 ELSE 组编号 END 组编号 FROM t1 WHERE 编号=@aid)
) a
ON CHARINDEX
(
',' +
(
CASE WHEN a.编号='' THEN b.编号 ELSE b.组编号 END
) + ','
,
',' + a.编号 + ',' + a.下属编号 + ','
)>0
END
GOGO
EXEC p 3
/*
1 aa 3
2 bb 1
4 dd 5*/EXEC p 4
/*
5 ee 4*/
GO
DROP TABLE t1,t2
GO
DROP PROC p
GO
set nocount on;
if not object_id('A') is null
drop table A
Go
Create table A([编号] int,[名称] nvarchar(4),[组编号] int,[下属编号] nvarchar(3))
Insert A
select 1,N'XX单位',null,N'3,5' union all
select 3,N'XX部门',1,null union all
select 4,N'XX部门',null,null union all
select 5,N'XX部门',1,null
Go
--> --> (Roy)生成測試數據
set nocount on;
if not object_id('B') is null
drop table B
Go
Create table B([编号] int,[名称] nvarchar(2),[单位编号] int)
Insert B
select 1,N'aa',3 union all
select 2,N'bb',1 union all
select 3,N'cc',2 union all
select 4,N'dd',5 union all
select 5,N'ee',4
Go
if object_id('p1') is not null
drop proc p1
go
create proc p1(@Code int )
as
select
*
from
B
where
exists(select 1 from A where (b.[单位编号]=@Code or ','+[下属编号]+',' like '%,'+rtrim(b.[单位编号])+',%') and [编号]=@Code )
or
exists(select 1 from A t1 join A t2 on t1.[组编号]=t2.[编号] where t1.[编号]=@Code and (','+t2.[下属编号]+',' like '%'+rtrim(b.[单位编号])+'%' or t1.[组编号]=b.[单位编号]))
go
--测试
exec p1 1
exec p1 3
exec p1 4
/*
编号 名称 单位编号
----------- ---- -----------
1 aa 3
2 bb 1
4 dd 5编号 名称 单位编号
----------- ---- -----------
1 aa 3
2 bb 1
4 dd 5编号 名称 单位编号
----------- ---- -----------
5 ee 4
*/
/*
1 aa 3
2 bb 1
4 dd 5
*/
EXEC p 3
/*
1 aa 3
2 bb 1
4 dd 5*/EXEC p 4
/*
5 ee 4*/