我有這樣的表格，我需要在表格中指定低于基本價格的價格。
id value price base
1 aaa 100
2 bbb 200
3 ccc 300 Yes
4 aaa 200 Yes
5 aaa 300
6 aaa 400
像這樣 ：
id value price status
1 aaa 100 down
2 bbb 200
3 ccc 300 base
4 aaa 200 base
5 aaa 300 up
6 aaa 400 up
使用此代碼，我可以獲得最大和最小價格值：
SELECT
min(price) as min_price
, max(price) as max_price
FROM
(
select *
From MyTable
) tmp;
uj5u.com熱心網友回復：
如果我正確理解了這個問題，以下陳述是您問題的可能解決方案：
桌子：
SELECT *
INTO MyTable
FROM (VALUES
(1, 'aaa', 100, NULL, CONVERT(varchar(5), NULL)),
(2, 'bbb', 200, NULL, CONVERT(varchar(5), NULL)),
(3, 'ccc', 300, 'Yes', CONVERT(varchar(5), NULL)),
(4, 'aaa', 200, 'Yes', CONVERT(varchar(5), NULL)),
(5, 'aaa', 300, NULL, CONVERT(varchar(5), NULL)),
(6, 'aaa', 400, NULL, CONVERT(varchar(5), NULL))
) v (id, value, price, base, status)
選擇陳述句：
SELECT
id,
value,
price,
base,
CASE
WHEN base = 'Yes' THEN 'base'
WHEN price < MAX(CASE WHEN base = 'Yes' THEN price END) OVER (PARTITION BY value) THEN 'down'
WHEN price > MAX(CASE WHEN base = 'Yes' THEN price END) OVER (PARTITION BY value) THEN 'up'
END AS status
FROM MyTable
ORDER By id
更新宣告：
; WITH cte AS (
SELECT
id,
value,
price,
base,
status,
CASE
WHEN base = 'Yes' THEN 'base'
WHEN price < MAX(CASE WHEN base = 'Yes' THEN price END) OVER (PARTITION BY value) THEN 'down'
WHEN price > MAX(CASE WHEN base = 'Yes' THEN price END) OVER (PARTITION BY value) THEN 'up'
END AS status2
FROM MyTable
)
UPDATE cte
SET status = status2
結果：
id value price base status
--------------------------
1 aaa 100 down
2 bbb 200
3 ccc 300 Yes base
4 aaa 200 Yes base
5 aaa 300 up
6 aaa 400 up
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/422059.html
標籤：