一句集大成的SQL

今天PM提了个变态的需求，算法很繁琐大致如下

l          Budget Light

System parameter: Budget Red – 20%; Budget Yellow - 10%

Grey: No value assigned to Budget

Green: Efforts <= Budget * (1-Budget Yellow)

Yellow: 1- Budget Yellow< Effort/Budget < 1+ Budget Red

Red: Effort >= Budget * (1+ Budget Red)

l          Schedule Light

Grey: No value assigned to Start Date or End Date of phase

Green: A.Start < P.Start and A.End < P.End

Yellow: A.Start > P.Start

Red: A.End > P.End

数据库我就不贴了，涉及的表太多，大家就宏观看下

 

 

由于项目采用迭代开发（其实一开始需求很简单，没想过，所以导致现在的迭代开发很痛苦）所以数据库表越加越多，后来觉得乱糟糟的，所以导致SQL极度难写。

为了写这句sql，我分了三步走

第一句，显示所有要显示的文本数据

SELECT ACTIVITY.ID,ACTIVITY.Name,ACTIVITY.[Type],STAFF.Name as pm, PROJECT.createdate,ACTIVITY.Status,PROJECT.EndDate, PROJECT.StartDate,projectType.name as projectType, technology.name as technology,region.name as region FROM ACTIVITY WITH(NOLOCK), STAFF WITH(NOLOCK), (SELECT PROJECT.* FROM PROJECT WITH(NOLOCK) JOIN HIERARCHY_PM WITH(NOLOCK) ON PROJECT.PM = HIERARCHY_PM.PM AND HIERARCHY_PM.Staff = '70012244' UNION SELECT PROJECT.* FROM PROJECT WITH(NOLOCK) JOIN (SELECT DISTINCT ProjectID FROM PROJECT_MEMBER WITH(NOLOCK) JOIN Staff S ON Member = S.ID JOIN HIERARCHY_SV WITH(NOLOCK) ON S.Supervisor = HIERARCHY_SV.SV AND HIERARCHY_SV.Staff = '70012244') MemberProjects ON PROJECT.ID = MemberProjects.ProjectID) PROJECT LEFT JOIN MasterData as projectType WITH(NOLOCK) on PROJECT.[Type]=projectType.id and projectType.typeid='Project Type' LEFT JOIN MasterData as technology WITH(NOLOCK) on PROJECT.Technology=technology.id and technology.typeid='Technology' LEFT JOIN MasterData as region WITH(NOLOCK) on PROJECT.Region=region.id and region.typeid='Region' WHERE ACTIVITY.id=PROJECT.id and STAFF.ID = PROJECT.PM AND ACTIVITY.status=1

呵呵，已经过复杂的了

第二句，满足上面需求中的第一条

select projectid,max(sl) as slfrom (select project_phase.projectId,case when plannedstartdate is null or plannedenddate is null or startdate is null or enddate is null then 1 else case when startdate<plannedstartdate or enddate<plannedenddate then 2 else case when startdate>plannedstartdate then 3 else 4 end end end as sl from project_phase) as allsl group by projectid

第三句，满足上面需求中的第二条

select bltable.id, case when bltable.budget is null then 1 else case when bltable.effort<=budget*(1-yv) then 2 else case when bltable.effort>=budget*(1+rv) then 4 else 3 end end end as bl from (select activity.id,project.budget,efforttable.effort,cast(red.paramValue as float)as rv,cast(yellow.paramValue as float)as yv from activity join project on project.id=activity.id left join (select activity as id,sum(effort) as effort from invoice group by activity ) as efforttable on efforttable.id=activity.id join sysparams as red on red.paramName='budget red' join sysparams as yellow on yellow.paramName='budget yellow') as bltable

最后将他们合并满足最终的显示要求

select * from (select isnull(bb.bl,1)as budgetLight,isnull(ss.sl,1)as scheduleLight,pp.id,pp.name,pp.type, pp.pm,pp.createdate,pp.status,pp.enddate,pp.startdate, pp.projectType,pp.technology,pp.region from (SELECT ACTIVITY.ID,ACTIVITY.Name,ACTIVITY.[Type],STAFF.Name as pm, PROJECT.createdate,ACTIVITY.Status,PROJECT.EndDate, PROJECT.StartDate,projectType.name as projectType, technology.name as technology,region.name as region FROM ACTIVITY WITH(NOLOCK), STAFF WITH(NOLOCK), (SELECT PROJECT.* FROM PROJECT WITH(NOLOCK) JOIN HIERARCHY_PM WITH(NOLOCK) ON PROJECT.PM = HIERARCHY_PM.PM AND HIERARCHY_PM.Staff = '70012244' UNION SELECT PROJECT.* FROM PROJECT WITH(NOLOCK) JOIN (SELECT DISTINCT ProjectID FROM PROJECT_MEMBER WITH(NOLOCK) JOIN Staff S ON Member = S.ID JOIN HIERARCHY_SV WITH(NOLOCK) ON S.Supervisor = HIERARCHY_SV.SV AND HIERARCHY_SV.Staff = '70012244') MemberProjects ON PROJECT.ID = MemberProjects.ProjectID) PROJECT LEFT JOIN MasterData as projectType WITH(NOLOCK) on PROJECT.[Type]=projectType.id and projectType.typeid='Project Type' LEFT JOIN MasterData as technology WITH(NOLOCK) on PROJECT.Technology=technology.id and technology.typeid='Technology' LEFT JOIN MasterData as region WITH(NOLOCK) on PROJECT.Region=region.id and region.typeid='Region' WHERE ACTIVITY.id=PROJECT.id and STAFF.ID = PROJECT.PM AND ACTIVITY.status=1) as pp left join (select projectid,max(sl) as sl from (select project_phase.projectId,case when plannedstartdate is null or plannedenddate is null or startdate is null or enddate is null then 1 else case when startdate<plannedstartdate or enddate<plannedenddate then 2 else case when startdate>plannedstartdate then 3 else 4 end end end as sl from project_phase) as allsl group by projectid) as ss on ss.projectid=pp.id left join (select bltable.id, case when bltable.budget is null then 1 else case when bltable.effort<=budget*(1-yv) then 2 else case when bltable.effort>=budget*(1+rv) then 4 else 3 end end end as bl from (select activity.id,project.budget,efforttable.effort,cast(red.paramValue as float)as rv,cast(yellow.paramValue as float)as yv from activity join project on project.id=activity.id left join (select activity as id,sum(effort) as effort from invoice group by activity ) as efforttable on efforttable.id=activity.id join sysparams as red on red.paramName='budget red' join sysparams as yellow on yellow.paramName='budget yellow') as bltable)as bb on bb.id=pp.id ) alldata where alldata.projectType like '%' or alldata.technology like '%' or alldata.region like '%' or alldata.name like '%' or alldata.pm like '%' ORDER BY alldata.createdate DESC

这句sql基本涵盖了sql中所有常用的语句和方法，哎~~写的眼都花了，还好我分三步走，不然有的苦了。效率觉得一般，谁有觉得更好的写法，可以发表评论。