我正在尝试将 MySQL 查询转换为 T-SQL 查询,而 SUM 语句中包含的 IF 语句让我感到困惑.有什么建议吗?
I'm attempting to convert a MySQL query to a T-SQL query and the IF statement that's enclosed within a SUM statement is tripping me up. Any suggestions?
SELECT
CMTS_RQ.[Dated],
CMTS_RQ.CMTS_Name,
Count(CMTS_RQ.CMTS_Name) AS emat_count,
Sum(if(CMTS_RQ.US_Pwr>=37 and CMTS_RQ.US_Pwr<=49)) AS us_pwr_good
FROM
CMTS_RQ
GROUP BY
CMTS_RQ.CMTS_Name,
CMTS_RQ.[Dated]
但我收到一个错误:
消息 156,级别 15,状态 1,第 5 行
关键字if"附近的语法不正确.
消息 102,级别 15,状态 1,第 5 行
')' 附近的语法不正确.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
T-SQL 没有内联"IF
语句 - 使用 CASE
代替:
T-SQL doesn't have a "inline" IF
statement - use a CASE
instead:
SELECT
CMTS_RQ.[Dated],
CMTS_RQ.CMTS_Name,
Count(CMTS_RQ.CMTS_Name) AS emat_count,
Sum(CASE
WHEN CMTS_RQ.US_Pwr >=37 AND CMTS_RQ.US_Pwr <= 49
THEN 1
ELSE 0
END) AS us_pwr_good
FROM
CMTS_RQ
GROUP BY
CMTS_RQ.CMTS_Name,
CMTS_RQ.[Dated]
因此,如果 CMTS_RQ.US_Pwr
的值是 >= 37 AND <= 49
然后将 SUM
加 1 - 否则0. 这是否为您提供了您想要的东西?
So if the value of CMTS_RQ.US_Pwr
is >= 37 AND <= 49
then add 1 to the SUM
- otherwise 0. Does that give you what you're looking for?
在 SQL Server 2012 和更新版本中,您可以使用新的 IIF
函数:
In SQL Server 2012 and newer, you can use the new IIF
function:
SUM(IIF(CMTS_RQ.US_Pwr >= 37 AND CMTS_RQ.US_Pwr <= 49, 1, 0)) AS us_pwr_good
这篇关于嵌入在 sum() 函数中的 T-SQL IF 语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持html5模板网!