問題描述

我正在嘗試將 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模板網！