mysql stdistance_SQL 条件语句 (IF, CASE WHEN, IFNULL)

SQL 条件语句 (IF, CASE WHEN, IFNULL)

1、IF

1.1 表达式：

IF( expr1 , expr2 , expr3 )

expr1条件，条件为true，则值是expr2 ，false，值就是expr3

示例;

SELECT

o.id,u.account,catagory.`name`,orderTime,detail.amount,periodtime,if(direction='0','看涨','看跌') directionName

FROM t_order_detail detail

LEFT JOIN t_order o ON o.id = detail.orderId

LEFT JOIN t_catagory catagory ON catagory.id = o.catId

LEFT JOIN t_user u ON u.id = o.userId

1.2  IF ELSE 做为流程控制语句使用

IF Boolean_expression

{ sql_statement | statement_block }

[ ELSE

{ sql_statement | statement_block } ]

参数说明：

Boolean_expression

返回True或False的表达式。如果布尔表达式包含SELECT语句，则SELECT语句必须用括号括起来

{ sql_statement | statement_block }

使用语句块定义的任何有效的Transact-SQL语句或语句分组。要定义语句块(批处理)，请使用流语言关键字BEGIN和END 组合。尽管所有Transact-SQL语句在BEGIN…END块中都是有效的，但某些Transact-SQL语句不应在同一批(语句块)中组合在一起。

示例1：

IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')

SELECT 'Weekend';

ELSE

SELECT 'Weekday';

注意：IF作为一条语句，在END IF后需要加上分号“;”以表示语句结束，其他语句如CASE、LOOP等也是相同的。

示例2：

USE AdventureWorks2012;

GO

DECLARE @AvgWeight decimal(8,2), @BikeCount int

IF

(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5

BEGIN

SET @BikeCount =

(SELECT COUNT(*)

FROM Production.Product

WHERE Name LIKE 'Touring-3000%');

SET @AvgWeight =

(SELECT AVG(Weight)

FROM Production.Product

WHERE Name LIKE 'Touring-3000%');

PRINT 'There are ' + CAST(@BikeCount AS varchar(3)) + ' Touring-3000 bikes.'

PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.';

END

ELSE

BEGIN

SET @AvgWeight =

(SELECT AVG(Weight)

FROM Production.Product

WHERE Name LIKE 'Touring-3000%' );

PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.' ;

END ;

GO

2、CASE WHEN

CASE表达式有两种格式：两种格式都支持可选的ELSE参数。

简单大小写表达式将一个表达式与一组简单表达式进行比较以确定结果。

搜索的CASE表达式计算一组布尔表达式以确定结果。

CASE可用于允许有效表达式的任何语句或子句中。例如，可以在SELECT、UPDATE、DELETE和SET等语句中使用CASE，也可以在SELECT\U list、in、WHERE、ORDER BY和HAVING等子句中使用CASE。

表达式：

CASE 列名

WHEN 条件 THEN 结果

ELSE 其他结果

END 别名

示例：

SELECT

CASE detail.`status` WHEN '0' THEN '未开仓'

WHEN '1' THEN '已开仓'

WHEN '2' THEN '已平仓'

ELSE '取消订单' END

status,

CASE o.type WHEN '0' THEN '单期'

WHEN '1' THEN '多期'

ELSE '策略' END

typeName

FROM t_order_detail detail

LEFT JOIN t_order o ON o.id = detail.orderId

示例：

SELECT CASE sva

WHEN 1 THEN '男'

ELSE '女'

END AS s

FROM table_name

WHERE sva != '';

官方示例1(SELECT)：

USE AdventureWorks2012;

GO

SELECT ProductNumber, Category =

CASE ProductLine

WHEN 'R' THEN 'Road'

WHEN 'M' THEN 'Mountain'

WHEN 'T' THEN 'Touring'

WHEN 'S' THEN 'Other sale items'

ELSE 'Not for sale'

END,

Name

FROM Production.Product

ORDER BY ProductNumber;

GO

官方示例2：

USE AdventureWorks2012;

GO

SELECT ProductNumber, Name, "Price Range" =

CASE

WHEN ListPrice = 0 THEN 'Mfg item - not for resale'

WHEN ListPrice < 50 THEN 'Under $50'

WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'

WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'

ELSE 'Over $1000'

END

FROM Production.Product

ORDER BY ProductNumber ;

GO

官方示例3：

SELECT BusinessEntityID, SalariedFlag

FROM HumanResources.Employee

ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC

,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;

GO

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL

ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName

ELSE CountryRegionName END;

官方示例4：

USE AdventureWorks2012;

GO

UPDATE HumanResources.Employee

SET VacationHours =

( CASE

WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40

ELSE (VacationHours + 20.00)

END

)

OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue,

Inserted.VacationHours AS AfterValue

WHERE SalariedFlag = 0;

官方示例5：

USE AdventureWorks2012;

GO

CREATE FUNCTION dbo.GetContactInformation(@BusinessEntityID int)

RETURNS @retContactInformation TABLE

(

BusinessEntityID int NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

ContactType nvarchar(50) NULL,

PRIMARY KEY CLUSTERED (BusinessEntityID ASC)

)

AS

-- Returns the first name, last name and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@BusinessEntityID = BusinessEntityID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Person

WHERE BusinessEntityID = @BusinessEntityID;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e

WHERE e.BusinessEntityID = @BusinessEntityID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Person.BusinessEntityContact AS bec

WHERE bec.BusinessEntityID = @BusinessEntityID)

THEN 'Vendor'

-- Check for store

WHEN EXISTS(SELECT * FROM Purchasing.Vendor AS v

WHERE v.BusinessEntityID = @BusinessEntityID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Customer AS c

WHERE c.PersonID = @BusinessEntityID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @BusinessEntityID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @BusinessEntityID, @FirstName, @LastName, @ContactType;

END;

RETURN;

END;

GO

SELECT BusinessEntityID, FirstName, LastName, ContactType

FROM dbo.GetContactInformation(2200);

GO

SELECT BusinessEntityID, FirstName, LastName, ContactType

FROM dbo.GetContactInformation(5);

官方示例6：

USE AdventureWorks2012;

GO

SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate

FROM HumanResources.Employee AS e

JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID

GROUP BY JobTitle

HAVING (MAX(CASE WHEN Gender = 'M'

THEN ph1.Rate

ELSE NULL END) > 40.00

OR MAX(CASE WHEN Gender = 'F'

THEN ph1.Rate

ELSE NULL END) > 42.00)

ORDER BY MaximumRate DESC;

官方示例7：

-- Uses AdventureWorks

SELECT ProductAlternateKey, Category =

CASE ProductLine

WHEN 'R' THEN 'Road'

WHEN 'M' THEN 'Mountain'

WHEN 'T' THEN 'Touring'

WHEN 'S' THEN 'Other sale items'

ELSE 'Not for sale'

END,

EnglishProductName

FROM dbo.DimProduct

ORDER BY ProductKey;

官方示例8：

-- Uses AdventureWorks

UPDATE dbo.DimEmployee

SET VacationHours =

( CASE

WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40

ELSE (VacationHours + 20.00)

END

)

WHERE SalariedFlag = 0;

3、ISNULL()、NVL()、IFNULL()、 NULLIF

3.1 表达式(MySQL)：

IFNULL( expr1 , expr2)

expr1 值不为 NULL 返回 expr1，否则返回 expr2    例如：

IFNULL(AA,0); --AA不为NULL则返回AA，否则返回 0

3.2  -- MsSQL的 ISNULL  功能一样

3.3  -- Oracle的 NVL 功能一样

3.4 表达式(MsSQL)： NULLIF 不一样

NULLIF ( expression , expression )

如果两个表达式不相等，则返回第一个表达式。如果表达式相等，则NULLIF返回第一个表达式类型的null值。

创建时间：2020.09.14　　更新时间：