一句集大成的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 regionFROM 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) PROJECTLEFT 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 nullthen 1 elsecase when startdate<plannedstartdate or enddate<plannedenddate then2 else case when startdate>plannedstartdate then3 else4 end end end as slfrom project_phase) as allslgroup by projectid

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

select bltable.id,case when bltable.budget is nullthen 1 elsecase when bltable.effort<=budget*(1-yv) then2 else case when bltable.effort>=budget*(1+rv) then4 else3 end end end as blfrom (select activity.id,project.budget,efforttable.effort,cast(red.paramValue as float)as rv,cast(yellow.paramValue as float)as yvfrom activityjoin project on project.id=activity.idleft join(select activity as id,sum(effort) as effort from invoice group by activity ) as efforttableon efforttable.id=activity.idjoin 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.regionfrom(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 regionFROM 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) PROJECTLEFT 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 ppleft 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 nullthen 1 elsecase when startdate<plannedstartdate or enddate<plannedenddate then2 else case when startdate>plannedstartdate then3 else4 end end end as slfrom project_phase) as allslgroup by projectid) as ss on ss.projectid=pp.idleft join (select bltable.id,case when bltable.budget is nullthen 1 elsecase when bltable.effort<=budget*(1-yv) then2 else case when bltable.effort>=budget*(1+rv) then4 else3 end end end as blfrom (select activity.id,project.budget,efforttable.effort,cast(red.paramValue as float)as rv,cast(yellow.paramValue as float)as yvfrom activityjoin project on project.id=activity.idleft join(select activity as id,sum(effort) as effort from invoice group by activity ) as efforttableon efforttable.id=activity.idjoin sysparams as red on red.paramName='budget red'join sysparams as yellow on yellow.paramName='budget yellow') as bltable)as bbon bb.id=pp.id ) alldatawhere 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中所有常用的语句和方法，哎~~写的眼都花了，还好我分三步走，不然有的苦了。效率觉得一般，谁有觉得更好的写法，可以发表评论。