我只想獲得一個在 2 個資料列之間具有數量范圍的行，如果沒有匹配范圍，則獲取具有空值的傭金行。
| 包裹ID | 從金額 | 金額 | 委員會 | 型別ID |
|---|---|---|---|---|
| 1 | 1 | 100 | 2 | 3 |
| 2 | 101 | 500 | 3 | 1 |
| 3 | 501 | 1000 | 4 | 2 |
| 4 | 空值 | 空值 | 6 | 1 |
詢問 -
DECLARE @Amount INT = 1010
SELECT fromamount,
toamount,
commission,
typeid
FROM package
WHERE ( ( @Amount BETWEEN fromamount AND toamount )
OR ( @Amount NOT BETWEEN fromamount AND toamount )
AND ( fromamount IS NULL
AND toamount IS NULL ) )
uj5u.com熱心網友回復：
只需(@Amount NOT BETWEEN FromAmount AND ToAmount)在OR:
DECLARE @Amount INT = 1010
SELECT FromAmount, ToAmount, Commission, TypeID
FROM Package
WHERE ((@Amount BETWEEN FromAmount AND ToAmount)
OR (FromAmount IS NULL AND ToAmount IS NULL))
uj5u.com熱心網友回復：
我會這樣寫：
SELECT FromAmount, ToAmount, Commission, TypeID
FROM Package
WHERE @Amount BETWEEN FromAmount AND ToAmount OR
NOT EXISTS (SELECT 1 FROM Package
WHERE @Amount BETWEEN FromAmount AND ToAmount) AND
FromAmount IS NULL AND ToAmount IS NULL;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/436637.html
上一篇：視窗函式與分組依據