如何在SQL SELECT语句中执行IF…THEN?
例如:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
如何在SQL SELECT语句中执行IF…THEN?
例如:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
当前回答
在这种情况下,案例陈述是您的朋友,采取以下两种形式之一:
简单的例子:
SELECT CASE <variable> WHEN <value> THEN <returnvalue>
WHEN <othervalue> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
扩展情况:
SELECT CASE WHEN <test> THEN <returnvalue>
WHEN <othertest> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
您甚至可以将case语句放在一个order by子句中进行真正的花式排序。
其他回答
您可以有两种选择来实现:
使用从SQL Server 2012引入的IIF:选择IIF((过时=“N”或InStock=“Y”),1,0)作为可销售产品,*来自产品使用Select Case:选择大小写当过时=“N”或InStock=“Y”时然后1其他0结束销售*FROM产品
我喜欢使用CASE语句,但问题是在SQLSelect中使用IF语句。我过去使用的是:
SELECT
if(GENDER = "M","Male","Female") as Gender
FROM ...
这就像Excel或sheets IF语句,其中有一个条件,后跟true条件,然后是false条件:
if(condition, true, false)
此外,您还可以嵌套if语句(但use应该使用CASE:-)
(注意:这在MySQL Workbench中有效,但在其他平台上可能不起作用)
有多种情况。
SELECT
(CASE
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1001' THEN 'DM'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1002' THEN 'GS'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1003' THEN 'MB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1004' THEN 'MP'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1005' THEN 'PL'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1008' THEN 'DM-27'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1011' THEN 'PB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1012' THEN 'UT-2'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1013' THEN 'JGC'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1014' THEN 'SB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1015' THEN 'IR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1016' THEN 'UT-3'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1017' THEN 'UT-4'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1019' THEN 'KR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1020' THEN 'SYB-SB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1021' THEN 'GR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1022' THEN 'SYB-KP'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1026' THEN 'BNS'
ELSE ''
END) AS OUTLET
FROM matrixcrm.Transact
CASE语句最接近SQL中的IF,并且在所有版本的SQL Server上都受支持。
SELECT CAST(
CASE
WHEN Obsolete = 'N' or InStock = 'Y'
THEN 1
ELSE 0
END AS bit) as Saleable, *
FROM Product
如果希望结果为布尔值,则只需使用CAST运算符。如果您对int感到满意,这将起作用:
SELECT CASE
WHEN Obsolete = 'N' or InStock = 'Y'
THEN 1
ELSE 0
END as Saleable, *
FROM Product
CASE语句可以嵌入到其他CASE语句中,甚至可以包含在聚合中。
SQL Server Denali(SQL Server 2012)添加了IIF语句,该语句也可用于访问(Martin Smith指出):
SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product
SQL Server中的简单if-else语句:
DECLARE @val INT;
SET @val = 15;
IF @val < 25
PRINT 'Hi Ravi Anand';
ELSE
PRINT 'By Ravi Anand.';
GO
SQL Server中嵌套的If…else语句-
DECLARE @val INT;
SET @val = 15;
IF @val < 25
PRINT 'Hi Ravi Anand.';
ELSE
BEGIN
IF @val < 50
PRINT 'what''s up?';
ELSE
PRINT 'Bye Ravi Anand.';
END;
GO