sql - 如何简化 sql 查询?
问题描述
我有以下查询,它在具有大量数据的 sql server 中不起作用。当我在 where 子句中使用具有 3 个组合的日期过滤器时,查询不起作用。
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 (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')
我尝试了许多解决方案来简化此查询。但是没有任何东西可以处理大量数据。它正在处理很长时间并获得超时执行。任何人都可以帮助简化这个查询吗?
根据建议,我对查询进行了优化,如下所示。
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 表中拥有多个角色。
解决方案
显然是多余的,但还要考虑您的 where 条件,尤其是在您的计数查询中。查看您的(StatusCode 和 SubStatusCode 或 SubStatusCode 或 SubStatusCode 或 ...)。如果您随后的任何 OR 条件为真,则将被视为真。查看 StatusCode = 'AWIP' 和 SubStatus = 'DRSO',这将被解释为
( true and false or false or true or false ) resulting in TRUE
或另一个查看 StatusCode = 'XYZ' 和 SubStatus = 'DRSO',这将被解释为
( false and false or false or true or false ) resulting in TRUE.
我认为您要完成的是(分别根据上面的示例
( 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 () 子句可以帮助简化这种歧义。
现在,通过重复,如果这是预先编写为子查询,按用户 ID 分组并获取您可以加入一次的计数
由于您已经在 SQL-Server 中使用“#”临时表,因此您可以预先查询这些结果,然后在没有用户 ID 的情况下再次 sum() 以获取每个类别的总计。
根据重复 STAFF 的评论,您需要获取唯一的 STAFF ID(未提供,因此我将整理列名。您显然可以调整它)。此外,由于传递关联(如果 a=b 和 b=c 则 a=c),我将加入 STAFF 表并获得 STAFF 唯一 ID。此外,通过删除“角色”的上下文,我还为 StaffUserName 创建了一个列。这将消除重复记录,因为它汇总了每个人的总数,而不是每个人的角色。
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
由于 Assigned、Reviewed 和 Signed 的预查询一次最多预聚合到一条用户记录,因此您无需在外层使用 group by。此外,对于已分配、已审核、已签名的“总计”,由于这些未分组,因此它们将始终是代表各个类别的整个合格记录池的单个记录。应该可以帮助您完成查询并在尝试时进行总计。
根据评论反馈更新。
我的查询中没有使用任何“不同”,您必须添加它。至于为什么,我只能假设一个用户ROLE ID可能关联多个Staff?如果您可以编辑您的原始帖子并分享一些代表此类角色和员工的样本数据可能会有所帮助(并做几个角色/员工以查看更好的相关性,显然没有真正的私人/机密)。这可能是多条记录的来源。
我的印象是“UserADID”是唯一用户,而不是用户角色,因为此 ID 已分配给请求详细信息。我只能猜测影响,但现在我想你可能有类似的东西
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个角色的成员。
etc
那么这里真正处理的是哪个“ID”。批准“角色”ID 1 是与请求详细信息相关联,还是与员工 X、Y、Z 相关联。
如果数据与我所拥有的一样,并且由于您缺乏样本数据,我怎么知道与哪个员工站点/指定相关的基础计数。总计代表 ROLD 用户 ID,而不是 STAFF 用户 ID。否则请告诉我,我可以编辑结果。
所以现在您声明同一用户有重复项!不是用户角色!在这种情况下,我们需要将每个预查询 temp 一直连接到 USER STAFF ID,而不是 ROLE ID。我已经修改了上面的查询。
推荐阅读
- vue.js - vue - 动态指令参数的值无效(预期的字符串或空值):未定义
- docker - 使用 Go 访问远程 docker 存储库
- azure - BlobNotFound 错误 - 重定向到默认文件
- django - 如何保护 Django 媒体网址?
- python - (Python) 捕获与对象的任何交互
- json - 从 HashTable 转换为 JSON
- python - 为什么我的程序在 Python 中停止而没有错误?
- excel - 数组中的名称范围
- node.js - 如何在 Hyperledger 1.4.4(示例)的 Node.js 中使用 setTransient 和 getTransient?
- maven - 我无法从命令提示符执行 testNG 测试套件