日期:2014-05-17 浏览次数:20630 次
USE test
GO
-->生成表A表
if object_id(N'A表') is not null
drop table [A表]
Go
Create table [A表]([dept] nvarchar(3),[comment] nvarchar(8))
Insert into [A表]
Select N'财务部',N'C1,D1'
Union all Select N'人事部',N'B1'
Union all Select N'采购部',N'H0,H1,H2'
-->生成表B表
if object_id(N'B表') is not null
drop table [B表]
Go
Create table [B表]([Type] nvarchar(2),[ID] nvarchar(2))
Insert into [B表]
Select N'会计',N'C1'
Union all Select N'仓库',N'D1'
Union all Select N'人事',N'B1'
Union all Select N'采购',N'H0'
Union all Select N'资材',N'H1'
Union all Select N'零件',N'H2'
Go
select * from [B表]
WHERE EXISTS(SELECT 1 FROM [A表]
WHERE CHARINDEX([B表].ID,[A表].comment)>0
AND [A表].dept=N'财务部'
)
/*
Type ID
---- ----
会计 C1
仓库 D1
*/
select * from B where exists(select 1 from A where dept='财务部' and ','+comment+',' like '%,'+b.ID+',%' )