如何简化sql查询? [英] How can I simplify the sql query?
问题描述
我有以下查询，该查询在具有大量数据的sql服务器中不起作用.当我在where子句中使用具有3种组合的日期过滤器时，查询不起作用.
I have the below query which is not working in sql server with large amount of data. The query is not working when I use date filter with 3 combinations in where clause.
IF OBJECT_ID('tempdb..#tempAllocStatus1') IS NOT NULL
DROP TABLE #tempAllocStatus1
SELECT Users.Name,REPLACE(Staff.Designation, 'IND ', '') as Designation, Staff.Office as Location,
(SELECT COUNT(ReqDt.ID) FROM tblTPRequestDetail ReqDt
INNER JOIN tblTPRequest Req ON ReqDt.RequestID = Req.Id
WHERE ReqDt.AssignedToID = Users.UserADID AND Req.TypeOfRequest = 1 AND ((ReqDt.StatusCode = 'COMP' or ReqDt.StatusCode = 'PCR') or (ReqDt.StatusCode = 'AWIP' and ReqDt.SubStatusCode='BENRE' or ReqDt.SubStatusCode='BENSF' or ReqDt.SubStatusCode='DRR' or ReqDt.SubStatusCode='DRSO' or ReqDt.SubStatusCode='RPSOFF'))) [Indian Benchmarking Assigned],
(SELECT COUNT(ReqDt.ID) FROM tblTPRequestDetail ReqDt
INNER JOIN tblTPRequest Req ON ReqDt.RequestID = Req.Id
WHERE ReqDt.ReviewerID = Users.UserADID AND Req.TypeOfRequest = 1 AND ((ReqDt.StatusCode = 'COMP' or ReqDt.StatusCode = 'PCR') or (ReqDt.StatusCode = 'AWIP' and ReqDt.SubStatusCode='BENSF' or ReqDt.SubStatusCode='DRSO' or ReqDt.SubStatusCode='RPSOFF' or SubStatusCode='RPC' or SubStatusCode='TPRPC'))) [Indian Benchmarking Reviewer],
(SELECT COUNT(ReqDt.ID) FROM tblTPRequestDetail ReqDt
INNER JOIN tblTPRequest Req ON ReqDt.RequestID = Req.Id
WHERE ReqDt.SignoffID = Users.UserADID AND Req.TypeOfRequest = 1 AND ((ReqDt.StatusCode = 'COMP' or ReqDt.StatusCode = 'PCR') or (ReqDt.StatusCode = 'AWIP' and ReqDt.SubStatusCode='RPSOFF' or ReqDt.SubStatusCode='SCPC' or ReqDt.SubStatusCode='TPSCPC'))) [Indian Benchmarking Signoff],
(SELECT COUNT(ReqDt.ID) FROM tblTPRequestDetail ReqDt
INNER JOIN tblTPRequest Req ON ReqDt.RequestID = Req.Id
WHERE ReqDt.AssignedToID = Users.UserADID AND Req.TypeOfRequest = 1 AND ((ReqDt.StatusCode = 'COMP' or ReqDt.StatusCode = 'PCR') or (ReqDt.StatusCode = 'AWIP' and ReqDt.SubStatusCode='BENRE' or ReqDt.SubStatusCode='BENSF' or ReqDt.SubStatusCode='DRR' or ReqDt.SubStatusCode='DRSO' or ReqDt.SubStatusCode='RPSOFF'))) +
(SELECT COUNT(ReqDt.ID) FROM tblTPRequestDetail ReqDt
INNER JOIN tblTPRequest Req ON ReqDt.RequestID = Req.Id
WHERE ReqDt.ReviewerID = Users.UserADID AND Req.TypeOfRequest = 1 AND ((ReqDt.StatusCode = 'COMP' or ReqDt.StatusCode = 'PCR') or (ReqDt.StatusCode = 'AWIP' and ReqDt.SubStatusCode='BENSF' or ReqDt.SubStatusCode='DRSO' or ReqDt.SubStatusCode='RPSOFF' or SubStatusCode='RPC' or SubStatusCode='TPRPC'))) +
(SELECT COUNT(ReqDt.ID) FROM tblTPRequestDetail ReqDt
INNER JOIN tblTPRequest Req ON ReqDt.RequestID = Req.Id
WHERE ReqDt.SignoffID = Users.UserADID AND Req.TypeOfRequest = 1 AND ((ReqDt.StatusCode = 'COMP' or ReqDt.StatusCode = 'PCR') or (ReqDt.StatusCode = 'AWIP' and ReqDt.SubStatusCode='RPSOFF' or ReqDt.SubStatusCode='SCPC' or ReqDt.SubStatusCode='TPSCPC'))) [Indian Benchmarking Total],
(SELECT COUNT(ReqDt.ID) FROM tblTPRequestDetail ReqDt
WHERE ReqDt.AssignedToID = Users.UserADID AND ((ReqDt.StatusCode = 'COMP' or ReqDt.StatusCode = 'PCR') or (ReqDt.StatusCode = 'AWIP' and ReqDt.SubStatusCode='BENRE' or ReqDt.SubStatusCode='BENSF' or ReqDt.SubStatusCode='DRR' or ReqDt.SubStatusCode='DRSO' or ReqDt.SubStatusCode='RPSOFF'))) [All Assigned],
(SELECT COUNT(ReqDt.ID) FROM tblTPRequestDetail ReqDt
WHERE ReqDt.ReviewerID = Users.UserADID AND ((ReqDt.StatusCode = 'COMP' or ReqDt.StatusCode = 'PCR') or (ReqDt.StatusCode = 'AWIP' and ReqDt.SubStatusCode='BENSF' or ReqDt.SubStatusCode='DRSO' or ReqDt.SubStatusCode='RPSOFF' or SubStatusCode='RPC' or SubStatusCode='TPRPC'))) [All Reviewer],
(SELECT COUNT(ReqDt.ID) FROM tblTPRequestDetail ReqDt
INNER JOIN tblTPRequest Req ON ReqDt.RequestID = Req.Id
WHERE ReqDt.SignoffID = Users.UserADID AND ((ReqDt.StatusCode = 'COMP' or ReqDt.StatusCode = 'PCR') or (ReqDt.StatusCode = 'AWIP' and ReqDt.SubStatusCode='RPSOFF' or ReqDt.SubStatusCode='SCPC' or ReqDt.SubStatusCode='TPSCPC'))) [All Signoff],
(SELECT COUNT(ReqDt.ID) FROM tblTPRequestDetail ReqDt
WHERE ReqDt.AssignedToID = Users.UserADID AND ((ReqDt.StatusCode = 'COMP' or ReqDt.StatusCode = 'PCR') or (ReqDt.StatusCode = 'AWIP' and ReqDt.SubStatusCode='BENRE' or ReqDt.SubStatusCode='BENSF' or ReqDt.SubStatusCode='DRR' or ReqDt.SubStatusCode='DRSO' or ReqDt.SubStatusCode='RPSOFF'))) +
(SELECT COUNT(ReqDt.ID) FROM tblTPRequestDetail ReqDt
WHERE ReqDt.ReviewerID = Users.UserADID AND ((ReqDt.StatusCode = 'COMP' or ReqDt.StatusCode = 'PCR') or (ReqDt.StatusCode = 'AWIP' and ReqDt.SubStatusCode='BENSF' or ReqDt.SubStatusCode='DRSO' or ReqDt.SubStatusCode='RPSOFF' or SubStatusCode='RPC' or SubStatusCode='TPRPC'))) +
(SELECT COUNT(ReqDt.ID) FROM tblTPRequestDetail ReqDt
INNER JOIN tblTPRequest Req ON ReqDt.RequestID = Req.Id
WHERE ReqDt.SignoffID = Users.UserADID AND ((ReqDt.StatusCode = 'COMP' or ReqDt.StatusCode = 'PCR') or (ReqDt.StatusCode = 'AWIP' and ReqDt.SubStatusCode='RPSOFF' or ReqDt.SubStatusCode='SCPC' or ReqDt.SubStatusCode='TPSCPC'))) [All Total]
INTO #tempAllocStatus1
FROM tblUserRolesMapping Users
LEFT JOIN [tblstaff] Staff ON Users.UserADID = Staff.AD_Id
LEFT JOIN tblTPRequestDetail UserAssigned ON UserAssigned.AssignedToID = Users.UserADID
LEFT JOIN tblTPRequestDetail UserReviewer ON UserReviewer.ReviewerID = Users.UserADID
LEFT JOIN tblTPRequestDetail UserSignoff ON UserSignoff.SignoffID = Users.UserADID
LEFT JOIN tblTPRequest TPRAssigned ON UserAssigned.RequestID = TPRAssigned.ID
LEFT JOIN tblTPRequest TPRReviewer ON UserReviewer.RequestID = TPRReviewer.ID
LEFT JOIN tblTPRequest TPRSignoff ON UserSignoff.RequestID = TPRSignoff.ID
WHERE Users.Active = 1 and Users.[RoleId] !=6
AND (TPRAssigned.crtddate >= '2017-04-01' and TPRAssigned.crtddate<= '2017-05-30')
OR (TPRReviewer.crtddate >= '2017-04-01' AND TPRReviewer.crtddate<='2017-05-30')
OR (TPRSignoff.crtddate >= '2017-04-01' AND TPRSignoff.crtddate<='2017-05-30' )
GROUP BY Users.UserADID, Users.Name, Staff.Designation,Staff.Office
SELECT * FROM #tempAllocStatus1 WHERE [All Total] > 0
UNION ALL
SELECT * FROM #tempAllocStatus1 WHERE [All Total] = 0
在查询的以下几行中我都遇到了这个问题.
And I am facing the issue in the below lines of the query.
AND (TPRAssigned.crtddate >= '2017-04-01' and TPRAssigned.crtddate<= '2017-05-30')
OR (TPRReviewer.crtddate >= '2017-04-01' AND TPRReviewer.crtddate<='2017-05-30')
OR (TPRSignoff.crtddate >= '2017-04-01' AND TPRSignoff.crtddate<='2017-05-30')
我尝试了许多解决方案来简化此查询.但是，大量数据都无法正常工作.它正在处理很长时间，并且正在执行超时.任何人都可以帮助简化此查询吗?
I have tried many solutions to simplify this query. But nothing is working with large amount of data. It is processing for long time and getting timeout execution. Can anyone please help to simplify this query?
根据建议，我对查询进行了如下优化.
As per the suggestion , I have optimized query as below.
select
ReqDt.AssignedToID,
COUNT(*) NumRecs
into
#tmpAssigned
from
tblTPRequestDetail ReqDt
INNER JOIN tblTPRequest Req
ON ReqDt.RequestID = Req.Id
AND Req.TypeOfRequest = 1
AND Req.crtddate >= '2017-04-01'
and Req.crtddate <= '2022-05-30'
WHERE
ReqDt.StatusCode IN ( 'COMP', 'PCR' )
OR ( ReqDt.StatusCode = 'AWIP'
and ReqDt.SubStatusCode IN ( 'BENRE', 'BENSF', 'DRR', 'DRSO', 'RPSOFF' )
)
GROUP BY
ReqDt.AssignedToID
select
ReqDt.ReviewerID,
COUNT(*) NumRecs
into
#tmpReviewed
from
tblTPRequestDetail ReqDt
INNER JOIN tblTPRequest Req
ON ReqDt.RequestID = Req.Id
AND Req.TypeOfRequest = 1
AND Req.crtddate >= '2017-04-01'
and Req.crtddate <= '2022-05-30'
WHERE
ReqDt.StatusCode IN ( 'COMP', 'PCR' )
OR ( ReqDt.StatusCode = 'AWIP'
and ReqDt.SubStatusCode IN ( 'BENSF', 'DRSO', 'RPSOFF', 'RPC', 'TPRPC' )
)
GROUP BY
ReqDt.ReviewerID
select
ReqDt.SignoffID,
COUNT(*) NumRecs
into
#tmpSigned
from
tblTPRequestDetail ReqDt
INNER JOIN tblTPRequest Req
ON ReqDt.RequestID = Req.Id
AND Req.TypeOfRequest = 1
AND Req.crtddate >= '2017-04-01'
and Req.crtddate <= '2022-05-30'
WHERE
ReqDt.StatusCode IN ( 'COMP', 'PCR' )
OR ( ReqDt.StatusCode = 'AWIP'
and ReqDt.SubStatusCode IN ( 'RPSOFF', 'SCPC', 'TPSCPC' )
)
GROUP BY
ReqDt.SignoffID
SELECT U.Name,
REPLACE(S.Designation, 'IND ', '') as Designation,
S.Office as Location,
coalesce( Assigned.NumRecs, 0 ) [Indian Benchmarking Assigned],
coalesce( Reviewed.NumRecs, 0 ) [Indian Benchmarking Reviewed],
coalesce( Signed.NumRecs, 0 ) [Indian Benchmarking Signed],
coalesce( Assigned.NumRecs, 0 ) + coalesce( Reviewed.NumRecs, 0 ) + coalesce( Signed.NumRecs, 0 ) [total Indian Benchmarking]
from
tblUserRolesMapping U
left JOIN [tblstaff] S
ON U.UserADID = S.AD_Id
left JOIN #tmpAssigned Assigned
on U.UserADID = Assigned.AssignedToID
left JOIN #tmpReviewed Reviewed
on U.UserADID = Reviewed.ReviewerID
left JOIN #tmpSigned Signed
on U.UserADID = Signed.SignoffID
WHERE
U.Active = 1
and U.RoleId !=6
and coalesce( Assigned.NumRecs, 0 ) + coalesce( Reviewed.NumRecs, 0 ) + coalesce( Signed.NumRecs, 0 ) !=0
order by U.Name
但是我得到了重复的记录.如何从结果中删除重复的记录.同样，具有USERADID的同一用户在tblUserRoleMapping表中可以具有多个角色.
But I am getting the duplicate records. How do I remove duplicate records from the result. Also the same user with USERADID can have more than one role in tblUserRoleMapping table.
推荐答案
显然是多余的，但也要考虑您的where条件，尤其是在计数查询中.查看您的(StatusCode和SubStatusCode或SubStatusCode或SubStatusCode或...).如果您的任何后续OR条件为true，则将其计为true.查看StatusCode ='AWIP'和SubStatus ='DRSO'，这将被解释为
Obviously redundant, but also consideration of your where conditions, especially in your counting queries. Looking at your (StatusCode and SubStatusCode or SubStatusCode or SubStatusCode or ...). If any of your subsequent OR conditions are true it will be counted as true. Looking at StatusCode = 'AWIP' and SubStatus = 'DRSO', this would be interpreted as
( true and false or false or true or false ) resulting in TRUE
或另一个查看StatusCode ='XYZ'和SubStatus ='DRSO'的情况，则应解释为
or another Looking at StatusCode = 'XYZ' and SubStatus = 'DRSO', this would be interpreted as
( false and false or false or true or false ) resulting in TRUE.
我想尝试完成的工作是(分别针对上述每个样本
What I THINK you are trying to accomplish is (respectively per samples above
( true and (false or false or true or false )) resulting in TRUE
( false and (false or false or true or false )) resulting in FALSE.
您要的主要状态代码='AWIP'，如果是这样，则仅在任何SUBSTATUS代码为以下任何一项时计数.如果这样做的话，您的括号不能正确容纳.通过更改为IN()子句可以帮助简化这种歧义.
where you want the primary status code = 'AWIP', and if so, then only count if any of the SUBSTATUS codes is any of the following. Your parenthesis were not accommodating correctly if this was intended. By changing to an IN () clause can help simplify this ambiguity.
现在，有了重复项，如果它是作为子查询预先编写的，则按用户ID分组并获得计数，您可以一次加入该计数
Now, with the duplication, if this is pre-written as a sub-query, grouped by user ID and get the counts you can join to it once
由于您已经在SQL Server中使用#"临时表，因此您可以预查询这些结果，然后再次进行sum()，而无需用户ID以获得每个类别的总计.
Since you are already using "#" temp tables in SQL-Server, you can prequery these results and then sum() again without user ID for the grand-total per category.
根据重复的STAFF的注释，您需要获取UNIQUE STAFF ID(未提供，因此我将列名进行了修改.您显然可以对其进行调整).另外，由于传递关联(如果a = b和b = c则a = c)，我将加入STAFF表并获取STAFF唯一ID.另外，通过删除角色"的上下文，我还为StaffUserName组成了一列.这将消除重复的记录，因为它汇总了每个人(而不是每个人的角色)的总数.
Per comment of duplicate STAFF, you need to get the UNIQUE STAFF ID (not provided so I will make the column name up. You can obviously adjust that). Also, due to transitive association (if a=b and b=c then a=c) I will join to the STAFF table and get the STAFF unique ID. Also, by removing context of the "Role", I also made up a column for the StaffUserName. This will eliminate the duplicate records because it rolls-up the totals per PERSON, not per the persons ROLE.
select
S.UniqueStaffID,
COUNT(*) NumRecs
into
#tmpAssigned
from
tblTPRequestDetail ReqDt
JOIN [tblstaff] S
ON ReqDt.AssignedToID = S.AD_Id
INNER JOIN tblTPRequest Req
ON ReqDt.RequestID = Req.Id
AND Req.TypeOfRequest = 1
-- apply the date filters directly to where you are getting queries
AND Req.crtddate >= '2017-04-01'
and Req.crtddate <= '2017-05-30'
WHERE
-- and only getting same criteria
ReqDt.StatusCode IN ( 'COMP', 'PCR' )
OR ( ReqDt.StatusCode = 'AWIP'
and ReqDt.SubStatusCode IN ( 'BENRE', 'BENSF', 'DRR', 'DRSO', 'RPSOFF' )
)
GROUP BY
S.UniqueStaffID
select
S.UniqueStaffID,
COUNT(*) NumRecs
into
#tmpReviewed
from
tblTPRequestDetail ReqDt
JOIN [tblstaff] S
ON ReqDt.AssignedToID = S.AD_Id
INNER JOIN tblTPRequest Req
ON ReqDt.RequestID = Req.Id
AND Req.TypeOfRequest = 1
-- apply the date filters directly to where you are getting queries
AND Req.crtddate >= '2017-04-01'
and Req.crtddate <= '2017-05-30'
WHERE
-- and only getting same criteria
ReqDt.StatusCode IN ( 'COMP', 'PCR' )
OR ( ReqDt.StatusCode = 'AWIP'
and ReqDt.SubStatusCode IN ( 'BENSF', 'DRSO', 'RPSOFF', 'RPC', 'TPRPC' )
)
GROUP BY
S.UniqueStaffID
select
S.UniqueStaffID,
COUNT(*) NumRecs
into
#tmpSigned
from
tblTPRequestDetail ReqDt
JOIN [tblstaff] S
ON ReqDt.AssignedToID = S.AD_Id
INNER JOIN tblTPRequest Req
ON ReqDt.RequestID = Req.Id
AND Req.TypeOfRequest = 1
-- apply the date filters directly to where you are getting queries
AND Req.crtddate >= '2017-04-01'
and Req.crtddate <= '2017-05-30'
WHERE
-- and only getting same criteria
ReqDt.StatusCode IN ( 'COMP', 'PCR' )
OR ( ReqDt.StatusCode = 'AWIP'
and ReqDt.SubStatusCode IN ( 'RPSOFF', 'SCPC', 'TPSCPC' )
)
GROUP BY
S.UniqueStaffID
SELECT
S.StaffUserName,
REPLACE(S.Designation, 'IND ', '') as Designation,
S.Office as Location,
coalesce( Assigned.NumRecs, 0 ) [Indian Benchmarking Assigned],
coalesce( Reviewed.NumRecs, 0 ) [Indian Benchmarking Reviewed],
coalesce( Signed.NumRecs, 0 ) [Indian Benchmarking Signed],
coalesce( AllAssigned.NumRecs, 0 ) [All Indian Benchmarking Assigned],
coalesce( AllReviewed.NumRecs, 0 ) [All Indian Benchmarking Reviewed],
coalesce( AllSigned.NumRecs, 0 ) [All Indian Benchmarking Signed],
coalesce( AllAssigned.NumRecs, 0 )
+ coalesce( AllReviewed.NumRecs, 0 )
+ coalesce( AllSigned.NumRecs, 0 ) [AllRecords]
from
JOIN [tblstaff] S
LEFT JOIN #tmpAssigned Assigned
on S.UniqueStaffID = Assigned.UniqueStaffID
JOIN
( select sum(NumRecs) NumRecs
from #tmpAssigned ) AllAssigned
on 1=1
LEFT JOIN #tmpReviewed Reviewed
on S.UniqueStaffID = Reviewed.UniqueStaffID
JOIN
( select sum(NumRecs) NumRecs
from #tmpReviewed ) AllReviewed
on 1=1
LEFT JOIN #tmpSigned Signed
on S.UniqueStaffID = Signed.UniqueStaffID
JOIN
( select sum(NumRecs) NumRecs
from #tmpSigned ) AllSigned
on 1=1
WHERE
U.Active = 1
and U.RoleId !=6
ORDER BY
S.StaffUserName,
S.Designation,
S.Office
由于分配的"，已审阅的"和签名的"预查询最多一次被预先聚合，一次只记录一个用户记录，因此您不需要在外部使用分组依据.同样，对于已分配，审核，签名的总计"，由于这些未分组，因此它们将始终是单个记录，代表各个类别的完整合格记录池.应该可以帮助您在尝试时完成查询和总计.
Since the pre-query of Assigned, Reviewed and Signed are pre-aggregated to at most, one user record at a time, you don't need to use a group by at the outer level. Also, for the "TOTAL" of assigned, reviewed, signed, since those are not grouped, they will always be a single record representing the entire qualified pool of records of respective categories. Should help you get your query done and totals as you were attempting.
根据评论反馈进行更新.
Update per comment feedback.
我在查询中没有使用任何区别"，您必须添加了该区别.至于为什么，我只能假设一个用户的ROLE ID可能与多个Staff相关联?如果您可以编辑原始帖子并共享一些表示此类ROLES和STAFF的SAMPLE数据，则可能会有所帮助(并担任几个角色/工作人员以查看更好的相关性，并且显然没有真正的私人/机密信息).这可能是多个记录的来源.
I am not using any "distinct" in my query and you must have added that. As for why, I can only assume that a single user ROLE ID may be associated with multiple Staff? If you can edit your original post and share some SAMPLE Data representing such ROLES and STAFF might help (and do a couple roles/staff to see better correlation and obviously nothing truly private/confidential). This is probably the source of multiple records.
我的印象是，"UserADID"是唯一用户，而不是用户ROLE，因为此ID已分配给请求详细信息.我只能猜测其影响，但现在我想您可能会遇到
I was under the impression that the "UserADID" was a unique user, not a user ROLE as this ID was assigned to the request detail. I can only guess the impact but am now thinking you might have something like
ApprovingRole ID = 1
Office 1 Staff person ID X
Office 2 Staff Person ID Y
Office 3 Staff Person ID Z
ReviewingRole ID = 2
Office 2 Staff person ID A
Office 2 Staff Person ID Y
Office 4 Staff person ID B
SigningRole ID = 3
Office 2 Staff Person ID Y
Office 4 Staff person ID B
在上面的样本数据"中，您可以看到职员"Y"是所有3个角色的成员.职员"B"是2个角色的成员.
In the above "sample data" above, you can see that staff person "Y" is a member of all 3 roles. Staff person "B" is member of 2 roles.
etc
那么这里真正处理了哪个"ID".是将批准角色" ID 1绑定到请求详细信息，还是将其绑定到X，Y，Z的工作人员.
So which "ID" is really handled here. Is the Approving "Role" ID 1 tied to the request detail or is it the STAFF person X, Y, Z tied to.
如果数据是我所拥有的，并且由于您缺少样本数据，我将如何知道与之相关的基础工作地点/名称.否则，总数代表ROLD用户ID，而不是STAFF用户ID.否则让我知道，我可以编辑结果.
If the data is as I have it, and from your lack of sample data, how would I know which staff site / designation the underlying counts associated with. The totals are otherwise representative of the ROLD User ID, not the STAFF User ID. Let me know otherwise and I can edit results.
因此，现在您声明同一用户有重复项！不是用户角色！在这种情况下，我们需要将每个预查询临时文件一直连接到USER STAFF ID，而不是ROLE ID.我已经修改了上面的查询.
So now you state there are duplicates for the same USER! not user ROLE! In that case, we need to have each prequery temp joined all the way to the USER STAFF ID, not the ROLE ID. I have modified the queries above.
这篇关于如何简化sql查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
