适用场景： 需要根据现有字段经过一定条件得到新的查询字段 相关语法： CASE WHEN 条件1 TEHN 结果1 WHEN 条件2 THEN 结果2 ...... ELSE 结果N END 练习代码：

1 SELECT  * , 2             ( '评分: ' + INFO.CMScore + '
评级: ' + INFO.CMGrade+'' ) AS CMEvaluate , 3             ( '评分: ' + INFO.ExamScore + '
评级: ' + INFO.ExamGrade+'' ) 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) INFO49             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_GradeStandard52             WHERE Status = 0  AND LB_Porject_ScoreCard.ReportScore BETWEEN MinValue AND  MaxValue AND SC_ID = dbo.LB_Porject_ScoreCard.SC_ID) INFO53             WHERE INFO.GSName <> '' ), '- -') ELSE '- -' END ) AS CMGrade  FROM      dbo.LB_Project54             LEFT JOIN dbo.LB_Porject_ScoreCard ON LB_Porject_ScoreCard.LB_Pro_ID = LB_Project.LB_Pro_ID55             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;