不说语句质量及难易度。。我想知道大家遇到这钟情况一般怎么处理???是分到不同包含查询语句的函数里面还是像我这样一条语句写出来???这条语句查询出来的都是一个页面上的所以“页签”所包含的内容,最后拼接如下:SELECT WF_w_pc.XXXX,w_pc_ProjectId,ISNULL(WF_w_pc.w_pc_AnnualPlan,'') w_pc_AnnualPlan,w_pc_ProjectName,Dept_Name,ISNULL(WF_R_BI.YYYYName,'') w_pc_Nature,ISNULL(WF_R_BI2.YYYYName,'') w_pc_SOURCE ,w_pc_StartDate,w_pc_EndDate,ISNULL(WF_w_pc_AP.w_pc_AP_Plan,'') w_pc_AP_Plan
,CASE WF_w_pc.w_pc_Status
WHEN 10 THEN '草稿'
WHEN 20 THEN '审核中'
WHEN 30 THEN '完成'
ELSE '' END
AS w_pc_Status
,w_pc_Backdrop,w_pc_MainContent,w_pc_TechLines,w_pc_ResultForm,w_pc_SocialBenefits,w_pc_EconomicBenefit,w_pc_TechBenefits,w_pc_CooperationForm,w_pc_CooperationByUnit
,ISNULL(WF_w_pc_P.w_pc_P_UserName,'') w_pc_P_UserName
,ISNULL(WF_w_pc_P.proHead,'') proHead
,ISNULL(WF_w_pc_P.proArticipants,'') proArticipants
FROM WF_Research_ProjectCharter WF_w_pc
LEFT JOIN WF_Research_PC_AnnualPlan WF_w_pc_AP
ON WF_w_pc.XXXX=WF_w_pc_AP.XXXX
LEFT JOIN WF_Research_BasicInfo WF_R_BI
ON WF_w_pc.w_pc_Nature=WF_R_BI.YYYYID AND WF_R_BI.YYYYType=1
LEFT JOIN WF_Research_BasicInfo WF_R_BI2
ON WF_w_pc.w_pc_Nature=WF_R_BI2.YYYYID AND WF_R_BI2.YYYYType=2
LEFT JOIN
(SELECT XXXX,w_pc_P_UserName=(STUFF((SELECT ',' + w_pc_P_USERNAME FROM WF_Research_PC_Participate
WHERE w_pc_P_Type=2 AND XXXX=pcp.XXXX FOR XML PATH('')),1,1,''))
,proHead=(STUFF((SELECT ',' + w_pc_P_USERNAME FROM WF_Research_PC_Participate
WHERE w_pc_P_Type=3 AND XXXX=pcp.XXXX FOR XML PATH('')),1,1,''))
,proArticipants=(STUFF((SELECT ',' + w_pc_P_USERNAME FROM WF_Research_PC_Participate
WHERE w_pc_P_Type<>3 AND XXXX=pcp.XXXX FOR XML PATH('')),1,1,''))
FROM WF_Research_PC_Participate pcp
GROUP BY XXXX) AS WF_w_pc_P
ON WF_w_pc.XXXX=WF_w_pc_P.XXXX
WHERE WF_w_pc.XXXX=@XXXX
,CASE WF_w_pc.w_pc_Status
WHEN 10 THEN '草稿'
WHEN 20 THEN '审核中'
WHEN 30 THEN '完成'
ELSE '' END
AS w_pc_Status
,w_pc_Backdrop,w_pc_MainContent,w_pc_TechLines,w_pc_ResultForm,w_pc_SocialBenefits,w_pc_EconomicBenefit,w_pc_TechBenefits,w_pc_CooperationForm,w_pc_CooperationByUnit
,ISNULL(WF_w_pc_P.w_pc_P_UserName,'') w_pc_P_UserName
,ISNULL(WF_w_pc_P.proHead,'') proHead
,ISNULL(WF_w_pc_P.proArticipants,'') proArticipants
FROM WF_Research_ProjectCharter WF_w_pc
LEFT JOIN WF_Research_PC_AnnualPlan WF_w_pc_AP
ON WF_w_pc.XXXX=WF_w_pc_AP.XXXX
LEFT JOIN WF_Research_BasicInfo WF_R_BI
ON WF_w_pc.w_pc_Nature=WF_R_BI.YYYYID AND WF_R_BI.YYYYType=1
LEFT JOIN WF_Research_BasicInfo WF_R_BI2
ON WF_w_pc.w_pc_Nature=WF_R_BI2.YYYYID AND WF_R_BI2.YYYYType=2
LEFT JOIN
(SELECT XXXX,w_pc_P_UserName=(STUFF((SELECT ',' + w_pc_P_USERNAME FROM WF_Research_PC_Participate
WHERE w_pc_P_Type=2 AND XXXX=pcp.XXXX FOR XML PATH('')),1,1,''))
,proHead=(STUFF((SELECT ',' + w_pc_P_USERNAME FROM WF_Research_PC_Participate
WHERE w_pc_P_Type=3 AND XXXX=pcp.XXXX FOR XML PATH('')),1,1,''))
,proArticipants=(STUFF((SELECT ',' + w_pc_P_USERNAME FROM WF_Research_PC_Participate
WHERE w_pc_P_Type<>3 AND XXXX=pcp.XXXX FOR XML PATH('')),1,1,''))
FROM WF_Research_PC_Participate pcp
GROUP BY XXXX) AS WF_w_pc_P
ON WF_w_pc.XXXX=WF_w_pc_P.XXXX
WHERE WF_w_pc.XXXX=@XXXX
2.从设计角度说,sql开发尽量采用动态sql方式,
这样可以把常用的数据对象的查询方法进行封装,
实际上,充分利用数据库的视图,表,函数和动态sql,也可以产生类似OOPL的效果,大幅度的减少数代码量
我想说 若是给你个300多行的SQL 而且到处的join ,union,case,聚合函数,这种还得了
我最近也在开发这种签字流程的业务系统,
sql语句也是相当的长,
实在觉得人为加太麻烦就用工具中的 format code=》format (ctrl+F11)
case when 还好 isnull比较有影响 前提是大数据量