SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TEST_M(A nvarchar(10),B nvarchar(10),C nvarchar(10),D int);
insert into TEST_M
values ('109','a','1',100),
('109','a','2',200),
('109','a','3',300),
('109','d','4',400),
('109','e','5',500),
('109','f','6',600),
('109','g','7',700),
('109','h','8',800),
('109','i','9',900);
9 rows affected

select *
from TEST_M
where 1=1
order by A,B


A
B
C
D


109
a
1
100

109
a
2
200

109
a
3
300

109
d
4
400

109
e
5
500

109
f
6
600

109
g
7
700

109
h
8
800

109
i
9
900


-- 權限控管群組 : E => 處代號 , F => 群組代號
CREATE TABLE TEST_K(E nvarchar(10),F nvarchar(10));
insert into TEST_K
values ('a','x'),
('d','x'),
('e','x'),
('g','y'),
('h','y'),
('f','z'),
('i','w');
7 rows affected

select *
from TEST_K
where 1=1


E
F


a
x

d
x

e
x

g
y

h
y

f
z

i
w


select K2.E
from TEST_K as K1
left join TEST_K as K2 on K2.F=K1.F
where 1=1
and K1.E='a'


E


a

d

e


select TEST_M.*
from TEST_M
where 1=1
and TEST_M.B in (
select K2.E
from TEST_K as K1
left join TEST_K as K2 on K2.F=K1.F
where 1=1
and K1.E='a'
)
order by A,B


A
B
C
D


109
a
1
100

109
a
2
200

109
a
3
300

109
d
4
400

109
e
5
500

A	B	C	D
109	a	1	100
109	a	2	200
109	a	3	300
109	d	4	400
109	e	5	500
109	f	6	600
109	g	7	700
109	h	8	800
109	i	9	900

A	B	C	D
109	a	1	100
109	a	2	200
109	a	3	300
109	d	4	400
109	e	5	500
109	f	6	600
109	g	7	700
109	h	8	800
109	i	9	900

A	B	C	D
109	a	1	100
109	a	2	200
109	a	3	300
109	d	4	400
109	e	5	500
109	f	6	600
109	g	7	700
109	h	8	800
109	i	9	900