當我運行這個查詢時
SELECT number, id, clientid, hash,
CASE WHEN expirydate IS NULL THEN
date
ELSE expirydate
END as date。
CASE公司 WHEN '' THEN
(
SELECT CONCAT_WS(' '/span>, firstname, lastname)
FROM tblcontacts
WHERE userid = tblclients.userid
and is_primary = 1
)
ELSE company END as company
FROM "tblestimates"
LEFT JOIN tblclients ON tblclients.userid=tblestimates.clientid
WHERE status != 3
and status ! = 4
AND CASE WHEN expirydate IS NULL THEN
(date BETWEEN '2021-06-27' AND '2021-08-08')
ELSE (expirydate BETWEEN '2021-06-27' and '2021-08-08')
結束。
我得到了這個錯誤：
Msg 156, Level 15, State 1, Line 7
關鍵字'BETWEEN'附近的語法不正確。
uj5u.com熱心網友回復：
不要在where條款中使用case。這是不尋常的，也是沒有必要的
and
(
(expirydate IS NULL AND [date] BETWEEN '2021-06-27' AND '2021-08-08')
OR expirydate BETWEEN '2021-06-27' and '2021-08-08')
BTW between is problematic. 最好使用（注意我的日期改為2021-08-09）
and
(
(expirydate IS NULL AND [date] > = '2021-06-27' AND [date] < '2021-08-09')
OR expirydate > = '2021-06-27' AND expirydate < '2021-08-09' >。
)
uj5u.com熱心網友回復：
一個CASE陳述句總是解決一些結果值，所以在邏輯上這將評估為 "true "或 "false"：
CASE
WHEN expirydate IS NULL THEN
(date BETWEEN '2021-06-27' AND '2021-08-08')
ELSE[/span
(expirydate BETWEEN '2021-06-27' AND '2021-08-08')
結束。
不幸的是，SQL Server沒有布爾資料型別，所以你不能直接使用比較的結果作為運算式。相反，你需要讓CASE陳述句回傳一個支持的型別，比如BIT：
CASE
WHEN expirydate IS NULL AND date BETWEEN '2021-06-27' and '2021-08-08'
THEN 1
WHEN expirydate IS NOT NULL AND expirydate BETWEEN '2021-06-27'/span> AND '2021-08-08'
THEN 1
ELSE 0
END
然后你可以比較這個結果，在你的WHERE子句中做一個運算式：
WHERE 1 =
CASE[/span
WHEN expirydate IS NULL AND date BETWEEN '2021-06-27' and '2021-08-08'
THEN 1
WHEN expirydate IS NOT NULL AND expirydate BETWEEN '2021-06-27'/span> AND '2021-08-08'
THEN 1
ELSE 0
END
但是，這種組合可以更自然地（也可能更有效地）用OR來寫，如果與AND組合，記得把它放在括號里：
where
(
expirydate IS NULL AND date BETWEEN '2021-06-27' and '2021-08-08'
OR
過期日期 IS NOT NULL AND到期日期 BETWEEN '2021-06-27' and '2021-08-08'
在這種特殊情況下，你可以用COALESCE來寫得更簡單，因為你要比較的是哪一列不是空的：
WHERE
COALESCE(expirydate, date) BETWEEN '2021-06-27' AND '2021-08-08'
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/314784.html
標籤：
上一篇：插入和注冊資料不顯示在資料庫表中visualstudio2019
下一篇：在SQL中基于條件創建另一個列