注意Transact-SQL中Case函数的两种用法导致不同的结果集
SELECT A.GCMC AS FNAME,A.JSDW AS FOWNER,case
when b.fid IS NULL then -1 else b.fid end as farea,
a.CONTENT AS FCONTENT,A.UPDATETIME AS FTIME,NULL AS FHREF,A.DELETED AS FISDEL
FROM Z_bidding A LEFT JOIN CCIC_AREA B ON charindex(b.fname,a.areaid)>0
和
SELECT A.GCMC AS FNAME,A.JSDW AS FOWNER,case b.fid
when NULL then -1 else b.fid end as farea,
a.CONTENT AS FCONTENT,A.UPDATETIME AS FTIME,NULL AS FHREF,A.DELETED AS FISDEL
FROM Z_bidding A LEFT JOIN CCIC_AREA B ON charindex(b.fname,a.areaid)>0
执行的结果是不一样的
A.使用带有简单 CASE 函数的 SELECT 语句
USE AdventureWorks;
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
B.使用带有简单 CASE 函数和 CASE 搜索函数的 SELECT 语句
USE AdventureWorks;
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
USE AdventureWorks;
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
B.使用带有简单 CASE 函数和 CASE 搜索函数的 SELECT 语句
USE AdventureWorks;
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