CREATE TABLE AAAA
(
id int,
salary numeric(16,2)
)
GO
INSERT INTO AAAA(ID) VALUES (1)
INSERT INTO AAAA(ID) VALUES (2)
INSERT INTO AAAA(ID) VALUES (3)
INSERT INTO AAAA(ID) VALUES (4)
INSERT INTO AAAA(ID) VALUES (5)
INSERT INTO AAAA(ID) VALUES (6)
INSERT INTO AAAA(ID) VALUES (7)
INSERT INTO AAAA VALUES (8,330.00)
下面是测试case:
Case 1:
SET ANSI_NULLS ON -- 那么NULL=NULL
SELECT
CASE WHEN salary=NULL THEN '''' ELSE salary END AS s
FROM AAAA
ORDER BY salary DESC;
/*
Success
*/
CASE 2:
SET ANSI_NULLS OFF --那么NULL!=NULL
SELECT
CASE WHEN salary=NULL THEN '''' ELSE salary END AS s
FROM AAAA
ORDER BY salary DESC;
/*
Fail
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
*/
Case 3:
SELECT
CASE WHEN salary is null THEN '''' ELSE salary END AS s
FROM AAAA
ORDER BY salary DESC;
/*
Fail
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
*/
Case 4: = Case 1
SELECT
CASE salary WHEN null THEN '''' ELSE salary END AS s
FROM AAAA
ORDER BY salary DESC;
/*
When SET ANSI_NULLS ON
Success
When SET ANSI_NULLS OFF
Fail
*/
Case 5:
SELECT
CASE WHEN salary is null THEN '' ELSE ltrim(salary) END AS s
FROM AAAA
ORDER BY salary DESC
/*
Success
*/
结论:
case when 下的每个可能性输出必须具有同样的data type.