不用担心city, plant不同, server_info 通过location_id查询dc_info_a数据
SELECT
s_tb.id,
s_tb.server_name,
s_tb.user_id_1,
user_id_1.USER_NAME AS user_name_1,
user_id_1.email AS user_email_1,
s_tb.user_id_2,
user_id_2.USER_NAME AS user_name_2,
user_id_2.email AS user_email_2,
dc_info.city AS city,
dc_info.plant AS plant,
dc_info.u AS u
FROM
server_list_a s_tb
LEFT JOIN user_info_a user_id_1 ON user_id_1.id= s_tb.user_id_1
LEFT JOIN user_info_a user_id_2 ON user_id_2.id= s_tb.user_id_2
LEFT JOIN (
SELECT DISTINCT
( id ),
city,
plant,
u = (
STUFF((
SELECT
',' + u
FROM
(
SELECT
a.id,
b.city,
b.plant,
b.u
FROM
(
SELECT
a.[id],
b.[location_id]
FROM
( SELECT [id], [location_id] = CAST ( '<v>' + REPLACE( [location_id], '-', '</v><v>' ) + '</v>' AS xml ) FROM server_list_a ) a OUTER APPLY (
SELECT
[location_id] = T.C.value ( '.', 'varchar(50)' )
FROM
a.[location_id].nodes ( '/v' ) AS T ( C )) b
) a,
dc_info_a b
WHERE
a.location_id = b.id
) temp
WHERE
id = fina.id FOR XML PATH ( '' )
),
1,
1,
''
)
)
FROM
(
SELECT
a.id,
b.city,
b.plant,
b.u
FROM
(
SELECT
a.[id],
b.[location_id]
FROM
( SELECT [id], [location_id] = CAST ( '<v>' + REPLACE( [location_id], '-', '</v><v>' ) + '</v>' AS xml ) FROM server_list_a ) a OUTER APPLY (
SELECT
[location_id] = T.C.value ( '.', 'varchar(50)' )
FROM
a.[location_id].nodes ( '/v' ) AS T ( C )) b
) a,
dc_info_a b
WHERE
a.location_id = b.id
) fina
) AS dc_info ON s_tb.id = dc_info.id