求优化sql,or in 速度太慢了,就查出来一条数据10秒 ,急求。。
selectdistinctC.[项目所属机构]AsProjectName,MIN(C.TaskBaseline0StartDate)asProjectStartDate...
select distinct C.[项目所属机构] As ProjectName,MIN(C.TaskBaseline0StartDate) as ProjectStartDate
from View_TaskAllInfo C where (C.ResourceNTAccount='user1' or 'user1' in(
SELECT
distinct B.ResourceNTAccount
FROM [ZhongJian].[dbo].[View_User_Group] A
left join ProjectServer_Reporting.dbo.MSP_EpmResource_UserView B on
A.UserAccount=B.ResourceNTAccount
where GroupName=N'项目副经理工作组' AND B.资源所属项目 like '%'+C.项目所属机构+'%'
))
group by C. [项目所属机构] 展开
from View_TaskAllInfo C where (C.ResourceNTAccount='user1' or 'user1' in(
SELECT
distinct B.ResourceNTAccount
FROM [ZhongJian].[dbo].[View_User_Group] A
left join ProjectServer_Reporting.dbo.MSP_EpmResource_UserView B on
A.UserAccount=B.ResourceNTAccount
where GroupName=N'项目副经理工作组' AND B.资源所属项目 like '%'+C.项目所属机构+'%'
))
group by C. [项目所属机构] 展开
1个回答
展开全部
-- 你的SQL 有问题吧?C.ResourceNTAccount='user1' or 'user1' in 应该改成 C.ResourceNTAccount='user1' or C.ResourceNTAccount in
建议: 子查询 SELECT
distinct B.ResourceNTAccount
FROM [ZhongJian].[dbo].[View_User_Group] A
left join ProjectServer_Reporting.dbo.MSP_EpmResource_UserView B on
A.UserAccount=B.ResourceNTAccount
where GroupName=N'项目副经理工作组' AND B.资源所属项目 like '%'+C.项目所属机构+'%'
中 可不可以 把最大化模糊匹配改成 最右匹配 或者 直接改造成 = ,
方案一:
我观察你的SQL,首先 select distinct C.[项目所属机构] As ProjectName,MIN(C.TaskBaseline0StartDate) as ProjectStartDat中的distinct 没有必要写,这样会消耗一部分时间
select C.[项目所属机构] As ProjectName,MIN(C.TaskBaseline0StartDate) as ProjectStartDate
from View_TaskAllInfo C where (C.ResourceNTAccount='user1' or C.ResourceNTAccount in(
SELECT
distinct B.ResourceNTAccount
FROM [ZhongJian].[dbo].[View_User_Group] A
left join ProjectServer_Reporting.dbo.MSP_EpmResource_UserView B on
A.UserAccount=B.ResourceNTAccount
where GroupName=N'项目副经理工作组' AND B.资源所属项目 like '%'+C.项目所属机构+'%'
))
group by C. [项目所属机构]
-- 方案一:
-- 若效果不佳,可以把整个SQL拆分两个部分
select select distinct C.[项目所属机构] As ProjectName,MIN(C.TaskBaseline0StartDate) as ProjectStartDate
from
(
select distinct C.[项目所属机构] As ProjectName,MIN(C.TaskBaseline0StartDate) as ProjectStartDate
from View_TaskAllInfo C where C.ResourceNTAccount = 'user1'
union all
select distinct C.[项目所属机构] As ProjectName,MIN(C.TaskBaseline0StartDate) as ProjectStartDate
from View_TaskAllInfo C where C.ResourceNTAccount in
SELECT
distinct B.ResourceNTAccount
FROM [ZhongJian].[dbo].[View_User_Group] A
left join ProjectServer_Reporting.dbo.MSP_EpmResource_UserView B on
A.UserAccount=B.ResourceNTAccount
where GroupName=N'项目副经理工作组' AND B.资源所属项目 like '%'+C.项目所属机构+'%'
) as t
柚鸥ASO
2024-03-16 广告
2024-03-16 广告
「柚鸥ASO」在ASO这块就做的蛮不错的,一直专注于应用商店优化,因为专注所以专业;专注应用商店下载量优化、评分优化、关键词排名优化、关键词覆盖、产品权重提升等等整体方案优化服务柚鸥网络-全球ASO优化服务商专注ASO优化已11年!(效果说...
点击进入详情页
本回答由柚鸥ASO提供
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询