sql - 我如何将结果显示为 SQL 列中的多个值
问题描述
表格1
Code Division IsActive Company
A A 1 Test1
B B 1 Test1
C C 1 Test1
D D 1 Test2
E E 1 Test2
F F 1 Test2
交易表2
Code Division
1 A
2 B
3 C
4 D
5 A,B
6 E,f
7 E
8 F
Select * from table2 where (Select code from table1 where company = 'Test1')
-- 用这个查询我不能得到列 (5(A,B)) 和 6(E,F))
所以我将查询调整为:
Select
*
from table2
where
(Select
code
from table1
where
contains(code, 'A')
or
contains(code, 'B')
)
我不想硬编码值(分区)我如何通过参数!
解决方案
您可以使用 SQL Server 2016 中的 STRING_SPLIT 执行此操作:
declare @table1 table (Code char(2), Division char(2), IsActive bit, Company nvarchar(100))
insert into @table1 values
('A', 'A', 1, 'Test1')
, ('B', 'B', 1, 'Test1')
, ('C', 'C', 1, 'Test1')
, ('D', 'D', 1, 'Test2')
, ('E', 'E', 1, 'Test2')
, ('F', 'F', 1, 'Test2')
declare @table2 table (Code char(2), Division varchar(100))
insert into @table2 values
(1, 'A')
, (2, 'B')
, (3, 'C')
, (4, 'D')
, (5, 'A,B')
, (6, 'E,f')
, (7, 'E')
, (8, 'F')
SELECT distinct t2.*
FROM @table2 t2
cross apply string_split(t2.Division, ',')
inner join @table1 t1 on t1.Division = [value]
where t1.Company = 'Test1'
SELECT t1.*, t2.*, [value] as Table2Division
FROM @table2 t2
cross apply string_split(t2.Division, ',')
inner join @table1 t1 on t1.Division = [value]