我可以在SQL中一起使用CASE和IF吗？ [英] Can I use CASE and IF together in SQL

问题描述

如何在SQL中一起使用CASE和IF ..

How to use CASE and IF together in SQL..

推荐答案

您还没有说明您使用的数据库。



以下是 MSSQL 案例表达文档的链接 Oracle 。



...希望它有所帮助。

You haven't stated what database you are using.

Here are links to the documentation for case expressions for MSSQL and Oracle.

... hope it helps.

这里需要注意的是......你的意思是我可以使用CASE 语句和IF 声明在一起？例如在存储过程中？



答案是是，例如这个sql完全有效（如果没有意义）

The thing to be careful of here is ... do you mean can I use a CASE statement and an IF statement together? For example in a Stored Procedure?

The answer to that is "Yes", for example this sql is perfectly valid (if meaningless)

DECLARE @temp int
DECLARE @dt date
DECLARE @res VARCHAR(10)
SET @temp = 3
SET @dt = '03-JAN-2015'
IF @temp = 3 
BEGIN
	set @res = CASE WHEN @dt = '01-JAN-2015' 
                   THEN 'Bank Holiday' ELSE 'Go to work' END
	PRINT @res
END

CASE也可以在sql select语句中用作子句，例如

SELECT CASE WHEN DATENAME(dw, recDate) = 'Friday' THEN 'Leave early'
            WHEN DATENAME(dw, recDate) = 'Monday' THEN 'Groan'
            ELSE 'Go to work' END
FROM CP1

但是，IF语句不能以相同的方式使用，所以如果问题是我可以在同一语句中使用CASE子句和IF子句，答案是否。例如，以下SQL将生成错误

However, the IF statement cannot be used in the same way, so if the question is can I use a CASE clause and an IF clause in the same statement the answer is "No". For example the following SQL will generate an error

SELECT IF DAY(recDate = 6)  'Leave early' END FROM CP1

可以使用IIF在where子句中 - 请参阅此 blog [ ^ ]但总的来说我会坚持CASE

It is possible to use IIF in a where clause - see this blog[^] but on the whole I would personally stick to CASE

这篇关于我可以在SQL中一起使用CASE和IF吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！