一个字段包含多值查询
A表
dept comment
财务部 C1,D1
人事部 B1
采购部 H0,H1,H2
B表
Type ID
会计 C1
仓库 D1
人事 B1
采购 H0
资材 H1
零件 H2
根据A表comment的值，查询B表
Select * from B表 Where ID in (Select * from A表 Where acomment='财务部') 多值查询
[解决办法]
,with T as(select a.dept , comment = substring(a.comment
, b.number , charindex(',' , a.comment + ',' , b.number) - b.number) from a join master..spt_values b on b.type='p' and b.number between 1 and len(a.comment) where substring(',' + a.comment , b.number , 1) = ',')
Select * from B Where ID in (Select comment from A Where dept='财务部')
[解决办法]
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+',%' )
