我有一个表格tblBooking
:
bkID bkCusID bkTotalAmount
---- ------- -------------
17 11 NULL
18 11 NULL
19 11 NULL
如果bkTotalAmount
为 null,则bkStatus
应该是一个0
,如果不是,则应1
:
bkID bkCusID bkStatus
---- ------- -------------
17 11
18 11
19 11
我已经使用COALESCE
将状态更改为0
但不知道,如果bkTotalAmount
不为空,如何将状态更改为1
:
SELECT bkID, bkCusID, COALESCE(bkTotalAmount, 0) AS bkStatus
FROM tblBooking
WHERE bkCusID = 11 AND bkTotalAmount IS NULL
只需使用case
:
SELECT bkID, bkCusID,
(CASE WHEN bkTotalAmount IS NULL THEN 0 ELSE 1 END) AS bkStatus
FROM tblBooking
WHERE bkCusID = 11 ;
如果您希望将其存储在表本身中,则可以使用更新。相反,我建议添加一个计算列:
alter table tblBooking add bkStatus as (CASE WHEN bkTotalAmount IS NULL THEN 0 ELSE 1 END);
使用 ISNULL 函数而不是 COALESCE
SELECT
bkID,
bkCusID,
ISNULL(bkTotalAmount+1, 0) AS bkStatus
FROM tblBooking WHERE bkCusID=11