[2019-11-07 14:50:15 最后更新]
[mssql/sql server]sql server中使用CASE IF IIF关键字拼凑一个字段值
例如
case when i.IcCode='' then '4524251987' + REPLACE(str(i.IID, 8), ' ', '0') else i.IcCode end IDENTITY_CARD_NO, --居民身份证号码
这其中使用了 case 关键字来做判断语句。按直觉我们觉得应该有 if 语句，但其际是 sql server 2012 后才有类似的 IIF 关键字。
参考 https://codedefault.com/2018/sql-server-how-do-i-perform-an-if-then-in-an-sql-select
--------------------------------------------------------
SQL Server中如何写类似的IF THEN的查询(SELECT)语句？
比如需要写类似以的SQL查询语句：
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
在SQL Server中如何实现呢？
方案一、使用 CASE 关键字
SELECT CAST(
CASE
WHEN Obsolete = 'N' or InStock = 'Y'
THEN 1
ELSE 0
END AS bit) as Saleable, *
FROM Product
以上语句返回的是一个布尔值，如何需要返回 int 类型呢，如下：
SELECT CASE
WHEN Obsolete = 'N' or InStock = 'Y'
THEN 1
ELSE 0
END as Saleable, *
FROM Product
方案二、使用 IIF 关键字
由于IIF 关键字是SQL Server 2012及上以版本才有的，所以本方案仅适合SQL Server 2012及以上版本，代码如下：
SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product
方案三
SELECT
(CASE
WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
ELSE 'NO'
END) as Salable
, *
FROM Product
方案四
IF EXISTS(SELECT *
FROM Northwind.dbo.Customers
WHERE CustomerId = 'ALFKI')
PRINT 'Need to update Customer Record ALFKI'
ELSE
PRINT 'Need to add Customer Record ALFKI'
IF EXISTS(SELECT *
FROM Northwind.dbo.Customers
WHERE CustomerId = 'LARSE')
PRINT 'Need to update Customer Record LARSE'
ELSE
PRINT 'Need to add Customer Record LARSE'
方案六
DECLARE @Product TABLE (
id INT PRIMARY KEY IDENTITY NOT NULL
,Obsolote CHAR(1)
,Instock CHAR(1)
)
INSERT INTO @Product ([Obsolote], [Instock])
VALUES ('N', 'N'), ('N', 'Y'), ('Y', 'Y'), ('Y', 'N')
;
WITH cte
AS
(
SELECT
'CheckIfInstock' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Instock], 'Y'), 1), 'N'), 0) AS BIT)
,'CheckIfObsolote' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Obsolote], 'N'), 0), 'Y'), 1) AS BIT)
,*
FROM
@Product AS p
)
SELECT
'Salable' = c.[CheckIfInstock] & ~c.[CheckIfObsolote]
,*
FROM
[cte] c
方案七
SELECT 1 AS Saleable, *
FROM @Product
WHERE ( Obsolete = 'N' OR InStock = 'Y' )
UNION
SELECT 0 AS Saleable, *
FROM @Product
WHERE NOT ( Obsolete = 'N' OR InStock = 'Y' )
