SQL 查询中case的运用
适用场景: 需要根据现有字段经过一定条件得到新的查询字段
相关语法: CASE WHEN 条件1 TEHN 结果1 WHEN 条件2 THEN 结果2 ...... ELSE 结果N END
练习代码:
1 SELECT * , 2 ( '评分: <span style=''font-weight:bold''>' + INFO.CMScore + '</span><br/>评级: <span style=''font-weight:bold''>' + INFO.CMGrade+'</span>' ) AS CMEvaluate , 3 ( '评分: <span style=''font-weight:bold''>' + INFO.ExamScore + '</span><br/>评级: <span style=''font-weight:bold''>' + INFO.ExamGrade+'</span>' ) AS ExamEvaluate 4 FROM ( SELECT dbo.LB_Project.LB_Pro_ID , dbo.LB_Project.BD_ID , LB_Project.LB_Pro_Code , 5 -- ISNULL((SELECT TOP 1 Emp_Name FROM dbo.IC_Employee WHERE Emp_ID=dbo.LB_Porject_ScoreCard.Emp_ID_Report),'- -') AS ReportEmp, 6 -- ISNULL((SELECT TOP 1 Emp_Name FROM dbo.IC_Employee WHERE Emp_ID=dbo.LB_Porject_ScoreCard.Emp_ID_Exam),'- -') AS ExamEmp, 7 (SELECT TOP 1 BDName FROM dbo.Busi_Definition WHERE BD_ID = dbo.LB_Project.BD_ID ) AS BDName , 8 (SELECT TOP 1 BDCode FROM dbo.Busi_Definition WHERE BD_ID = dbo.LB_Project.BD_ID ) AS BDCode , 9 dbo.LB_Project.Cust_ID , 10 (SELECT TOP 1 Cust_Name FROM dbo.Cust_Main WHERE Cust_ID = dbo.LB_Project.Cust_ID) AS CustName , 11 (SELECT TOP 1 IDCard FROM dbo.Cust_Main WHERE Cust_ID = dbo.LB_Project.Cust_ID) AS CustIDCard , 12 --dbo.LB_Project.Dept_ID , 13 (SELECT TOP 1 Dept_Name FROM dbo.IC_Departments WHERE Dept_ID = LB_Project.Dept_ID) AS DeptName , 14 dbo.LB_Project.Emp_ID_ZB , 15 (SELECT TOP 1 Emp_Name FROM dbo.IC_Employee WHERE Emp_ID = LB_Project.Emp_ID_ZB ) AS EmpName_ZB , 16 dbo.LB_Project.Emp_ID_XB , 17 (SELECT TOP 1 Emp_Name FROM dbo.IC_Employee WHERE Emp_ID = LB_Project.Emp_ID_XB ) AS EmpName_XB , 18 dbo.LB_Project.LoanAmount , 19 --dbo.LB_Project.TermValue , 20 --dbo.LB_Project.LoanUses , 21 --dbo.LB_Project.TermUnit , 22 (CAST(TermValue AS NVARCHAR(10)) + ( CASE TermUnit WHEN '月' THEN '个' + TermUnit ELSE TermUnit END ) ) AS Term , 23 (CAST(CAST(RateValue AS DECIMAL(6,2)) AS NVARCHAR(10)) + '%/' + RateUnit ) AS Rate , 24 --dbo.LB_Project.RateValue , dbo.LB_Project.RateUnit , dbo.LB_Project.RM_ID , 25 (SELECT TOP 1 RM_Name FROM dbo.Set_RepayMent WHERE RM_ID = LB_Project.RM_ID) AS RMName , 26 --dbo.LB_Project.GM_ID , 27 (SELECT '['+GM_Name+']' FROM dbo.Set_GuaranteeMethod WHERE GM_ID IN (SELECT Value FROM dbo.SplitString(LB_Project.GM_ID,',',1) ) FOR XML PATH('')) AS GMName , 28 --dbo.LB_Project.VerifyStatus , 29 dbo.LB_Project.CreateTime , 30 dbo.LB_Project.Status , 31 (CASE dbo.LB_Project.Status WHEN 0 THEN '待确认' WHEN 1 THEN '待复核' WHEN 2 THEN '已评分' ELSE '- -' END ) StatusName , 32 --dbo.LB_Project_JRWPFD.LB_Pro_JP_ID , 33 --dbo.LB_Project_JRWPFD.MM_ID , 34 (CASE WHEN EXISTS(SELECT 1 FROM dbo.Busi_Definition WHERE BD_ID=dbo.LB_Project.BD_ID AND BDCode='BD_CODE_JRWPFD') 35 THEN (SELECT TOP 1 MM_Name FROM dbo.Market_Main WHERE MM_ID = LB_Project_JRWPFD.MM_ID) 36 WHEN EXISTS(SELECT 1 FROM dbo.Busi_Definition WHERE BD_ID=dbo.LB_Project.BD_ID AND BDCode='BD_CODE_ZHLGCYD') 37 THEN (SELECT TOP 1 MarketName FROM dbo.LB_Project_ZHLGCYD) ELSE '- -' END ) AS MMName , 38 --dbo.LB_Project_JRWPFD.S_BC_ID , 39 (CASE WHEN EXISTS(SELECT 1 FROM dbo.Busi_Definition WHERE BD_ID=dbo.LB_Project.BD_ID AND BDCode='BD_CODE_JRWPFD') 40 THEN (SELECT TOP 1 Name FROM dbo.Set_BusinessCategory WHERE S_BC_ID = dbo.LB_Project_JRWPFD.S_BC_ID) 41 WHEN EXISTS(SELECT 1 FROM dbo.Busi_Definition WHERE BD_ID=dbo.LB_Project.BD_ID AND BDCode='BD_CODE_ZHLGCYD') 42 THEN (SELECT TOP 1 CategoryArea FROM dbo.LB_Project_ZHLGCYD) ELSE '- -' END) AS CateGoryName , 43 --dbo.LB_Project_JRWPFD.Paving , 44 --dbo.LB_Project_JRWPFD.ValuationDetail , 45 --dbo.LB_Project_JRWPFD.Valuation , 46 (CASE WHEN dbo.LB_Project.Status = 2 THEN CONVERT(NVARCHAR(10), dbo.LB_Porject_ScoreCard.ExamScore)
ELSE '- -' END ) AS ExamScore , 47 (CASE WHEN LB_Porject_ScoreCard.Status = 2 THEN ISNULL(( SELECT TOP 1 GSName FROM ( SELECT GSName FROM dbo.SC_GradeStandard 48 WHERE Status = 0 AND LB_Porject_ScoreCard.ExamScore BETWEEN MinValue AND MaxValue AND SC_ID = dbo.LB_Porject_ScoreCard.SC_ID) INFO 49 WHERE INFO.GSName <> '' ), '- -') ELSE '- -' END ) AS ExamGrade , 50 (CASE WHEN dbo.LB_Project.Status > 0 THEN CONVERT(NVARCHAR(10), dbo.LB_Porject_ScoreCard.ReportScore) ELSE '- -' END ) AS CMScore , 51 (CASE WHEN LB_Porject_ScoreCard.Status > 0 THEN ISNULL(( SELECT TOP 1 GSName FROM ( SELECT GSName FROM dbo.SC_GradeStandard 52 WHERE Status = 0 AND LB_Porject_ScoreCard.ReportScore BETWEEN MinValue AND MaxValue AND SC_ID = dbo.LB_Porject_ScoreCard.SC_ID) INFO 53 WHERE INFO.GSName <> '' ), '- -') ELSE '- -' END ) AS CMGrade FROM dbo.LB_Project 54 LEFT JOIN dbo.LB_Porject_ScoreCard ON LB_Porject_ScoreCard.LB_Pro_ID = LB_Project.LB_Pro_ID 55 LEFT OUTER JOIN dbo.LB_Project_JRWPFD ON dbo.LB_Project_JRWPFD.LB_Pro_ID = dbo.LB_Project.LB_Pro_ID 56 LEFT JOIN dbo.LB_Project_ZHLGCYD ON LB_Project_ZHLGCYD.LB_Pro_ID = LB_Project.LB_Pro_ID) INFO;
喜欢 C#、SQL、Web