如何在SQL Server的布尔值中访问UNKNOWN？

Question

嗨，我了解到sql服务器的BIT有真，假和未知。例如，比较1 > NULL产生unknown 。

我知道我可以间接检查它：如果x is null or y is null，那么比较x > y必须unknown。

有没有办法直接访问unknown？例如

select *
into #t
from (
    SELECT 1 as [x], 1 as [y] UNION ALL
    SELECT 1 as [x], 2 as [y] UNION ALL
    SELECT 1 as [x], NULL as [y]
) as a

SELECT *
from #t
--x y
--1 1
--1 2
--1 NULL
select *
    ,/* ???? */ as [is x > y]
from #t
--want to have:
--x y      is x > y
--1 1      0
--1 2      0
--1 NULL   unknown

Answer 1

这将起作用：

select *
into #t
from (
    SELECT 1 as [x], 1 as [y] UNION ALL
    SELECT 1 as [x], 2 as [y] UNION ALL
    SELECT 1 as [x], NULL as [y]
) as a

SELECT *
from #t
--x y
--1 1
--1 2
--1 NULL
select *,
    case  
        when x > y then '1' 
        when x is null or y is null then 'unknown'
        else '0' 
        end as [is x > y]
from #t
--x y    is x > y
--1 1    0
--1 2    0
--1 NULL unknown
-- Don't forget to delete your temp table when done. 
drop table  #t 

Answer 2

您需要

一个带有IS NULL谓词的CASE表达式来返回字符串"unknown"作为最后一列值，这也需要其他"0"和"1"值作为varchar文本，以避免将"未知"字符串文字隐式转换为int。

SELECT
      x
    , y
    , CASE WHEN x > y THEN '1' WHEN x <= y THEN '0' ELSE 'unknown' END AS [is x > y]
FROM #t;