我的查询需要 10 秒来执行如何减少时间，并且它有许多子查询如何克服这一点

Question

SELECT        
p.ID, p.EMAIL, 
'Sales : ' + STUFF((SELECT ', ' + coordinator
FROM tbl_clientCoordinators l
WHERE type = 'SALES' AND p.ID = l.client_ID 
FOR XML PATH('')), 1, 1, '') + ' |  ' + 'R.M : ' + 
ISNULL(STUFF((SELECT ', ' + coordinator
FROM tbl_clientCoordinators l
WHERE type = 'RELATIONSHIP MANAGER' AND p.ID = l.client_ID 
FOR XML PATH('')), 1, 1, ''), 'N/A') + ' |  ' + 'P.O : ' + 
ISNULL(STUFF((SELECT ', ' + coordinator
FROM tbl_clientCoordinators l
WHERE type = 'PROCESSING OFFICER' AND p.ID = l.client_ID 
FOR XML PATH('')), 1, 1, ''), 'N/A') + ' |  ' + 'P.C : ' + 
ISNULL(STUFF((SELECT ', ' + coordinator
FROM tbl_clientCoordinators l
WHERE type = 'PROCESSING CONSULTANT' AND p.ID = l.client_ID 
FOR XML PATH('')), 1, 1, ''), 'N/A') + ' |  ' + 'P.M : ' + 
ISNULL(STUFF((SELECT ', ' + coordinator
FROM tbl_clientCoordinators l
WHERE type = 'PROCESSING MANAGER' AND p.ID = l.client_ID 
FOR XML PATH('')), 1, 1, ''), 'N/A') + ' |  ' + 'A.C : ' + 
ISNULL(STUFF((SELECT ', ' + coordinator
FROM tbl_clientCoordinators l
WHERE type = 'ASSISTANT CONSULTANT' AND p.ID = l.client_ID 
FOR XML PATH('')), 1, 1, ''), 'N/A') AS Cordinators
FROM
tbl_client p

Answer 1

似乎为了简单起见，试图做多件事。您应该加入tbl_clientCoordinators然后应该具有基于类型的大小写。

如果您落入 4 个条件，那么您的查询将联接数据 4 次。应该是 1 次

SELECT        
p.ID, p.EMAIL, 
'Sales : ' + ', ' + cco.coordinator + ' | ' + 
(CASE cco.type WHEN  'SALES'  THEN 'R.M : ' 
WHEN  'RELATIONSHIP MANAGER' THEN 'P.O : '
WHEN  'PROCESSING OFFICER' THEN 'P.C : '
WHEN  'PROCESSING CONSULTANT' THEN 'P.M : '
WHEN  'PROCESSING MANAGER' THEN 'A.C : '
WHEN 'ASSISTANT CONSULTANT' THEN 'N/A'
END) AS Cordinators
FROM
tbl_client p
INNER JOIN tbl_clientCoordinators cco on cco.client_ID = p.ID