如何优化这个 SQL 查询？

Question

我有 3 个表：

• CRSTask (ID,parentID)
• CRSTaskReceivers (ID,tskID,receiverID)
• UserNames (id,name)

...CRSTask 和 CRSTaskReceivers 之间的一对多关系
在 UserNames 和 CRSTaskReceivers 之间一对一

tasks   
ID   parent  
1     null    
10     1      
50     1

taskReceivers  
id      taskID    receiverID  
1        1          4(john)  
1        10         2(mike)  
1        50         3(brand)  

我需要这样的结果：

taskid    Receivers
------------------- 
1           jone,mike,brand   

ONLY FOR PARENT TaskS IT WILL CONCATE RECEIVERS

Answer 1

SQL Server 2005+：

---

SELECT t.id AS taskid,
       STUFF((SELECT ','+ x.name
                FROM (SELECT COALESCE(pu.[ArabicName], aut.Name) AS name
                        FROM CRSTaskReceivers tr 
                        JOIN AD_USER_TBL aut ON aut.id = tr.receiverid
                   LEFT JOIN PORTAL_USERS pu ON pu.id = aut.id
                       WHERE tr.crstaskid = t.id
                         AND tr.receivertype = 1
                      UNION
                      SELECT agt.name
                        FROM CRSTaskReceiver tr
                        JOIN AD_GROUP_TBL sgt ON agt.id = tr.receiverid
                       WHERE tr.receivertype = 3
                         AND tr.crstaskid = t.id) x
         FOR XML PATH('')), 1, 1, '')
  FROM CRSTasks t

不需要该函数。

Answer 2

除了奇怪的字符串连接之外，它看起来确实可以在一个查询而不是四个查询中完成所有操作。在联接中具有多个条件是完全可以的。一些事情：

FROM   CRSTaskReceiver
   INNER JOIN CRSTask
        ON  CRSTaskReceiver.CRSTaskID = CRSTask.ID
   INNER JOIN CRS_BuiltinGroup
        ON  CRSTaskReceiver.ReceiverID = CRS_BuiltinGroup.ID AND CRSTaskReceiver.ReceiverType = 4
WHERE  CRSTask.ParentTask = @TaskID

此外，该函数的以下部分似乎什么也没做。它的目的是什么？

DECLARE @tmpLength INT
SET @tmpLength = 0
SET @tmpLength = LEN(@tmp)
IF @tmpLength > 0
BEGIN
    SET @tmp = SUBSTRING(@tmp, 0, @tmpLength)
END