本文介绍了如何在sql server 2005中为每个语句设置多个case语句(3个或更多)?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在sql server 2005中为每个语句设置多个case语句(3个或更多)?
( CASE WHEN E.Mode = ' U' 那么 ISNULL(SUM(ES.SALARY), 0 )) AS PBFSALARY ELSE ' ' END ) AS PBFSALARY,( CASE WHEN E.Mode = ' C' 那么 ISNULL(SUM(ES.SALARY), 0 ) ELSE ' ' END ) AS CREDITSALARY,( CASE WHEN E.Mode = ' M' 那么 ISNULL(SUM(ES.SALARY), 0 ) ELSE ' ' END ) AS DEBITSALARY,将varchar转换为数字问题。
解决方案SELECT ISNULL(SUM(PBFSALARY), 0 )PBFSALARY, ISNULL(SUM(CREDITSALARY), 0 )CREDITSALARY, ISNULL(SUM(DEBITSALARY), 0 )DEBITSALARY FROM ( SELECT ( CASE WHEN E.Mode = ' U' 那么 ES.SALARY ELSE 0 END ) AS PBFSALARY,( CASE WHEN E.Mode = ' C' 那么 ES.SALARY ELSE 0 END ) AS CREDITSALARY,( CASE WHEN E.Mode = ' M' 那么 ES.SALARY ELSE 0 END ) AS DEBITSALARY FROM yourTable ) AS a
快乐编码! :)
改为根据您的需要调整以下内容: 选择 (选择 SUM(isnull(薪水, 0 )) FROM table1 其中 mode = ' u') AS U ,(选择 SUM(isnull(薪水, 0 )) FROM table1 其中 mode = ' c') AS ç
How to set multiple case statements (3 or more) with alias for each in sql server 2005?
(CASE WHEN E.Mode ='U' THEN ISNULL(SUM(ES.SALARY),0)) AS PBFSALARY ELSE '' END) AS PBFSALARY, (CASE WHEN E.Mode ='C' THEN ISNULL(SUM(ES.SALARY),0) ELSE '' END) AS CREDITSALARY, (CASE WHEN E.Mode ='M' THEN ISNULL(SUM(ES.SALARY),0) ELSE '' END) AS DEBITSALARY,Getting error conversion of varchar to numeric issue.
解决方案 SELECT ISNULL(SUM(PBFSALARY),0) PBFSALARY, ISNULL(SUM(CREDITSALARY),0) CREDITSALARY, ISNULL(SUM(DEBITSALARY),0) DEBITSALARY FROM ( SELECT (CASE WHEN E.Mode ='U' THEN ES.SALARY ELSE 0 END) AS PBFSALARY, (CASE WHEN E.Mode ='C' THEN ES.SALARY ELSE 0 END) AS CREDITSALARY, (CASE WHEN E.Mode ='M' THEN ES.SALARY ELSE 0 END) AS DEBITSALARY FROM yourTable ) AS aHappy Coding! :)
Adapt the following to your need instead: select (select SUM(isnull(salary, 0)) FROM table1 where mode='u') AS U , (select SUM(isnull(salary, 0)) FROM table1 where mode='c') AS c更多推荐
如何在sql server 2005中为每个语句设置多个case语句(3个或更多)?
发布评论