我的 SQL Server 表看起來像這樣
ID a_Toyota a_Mazda a_Nissan a_Kia a_Honda a_Subaru SoldCar CarOrder
1 8000 7000 6200 8500 6500 7000 Mazda NULL
2 4000 5000 4500 3500 3500 5000 Mazda NULL
3 5400 5000 4500 5500 5500 4600 Mazda NULL
4 5600 6300 7500 8200 6500 7300 Mazda NULL
5 8500 7400 7400 6500 9500 9000 Mazda NULL
6 9900 8000 9900 7300 8100 8000 Mazda NULL
我想更新 CarOrder 欄位，因此它具有與其他汽車價格相比的已售汽車價格的順序。
因此，對于 ID 1 訂購的汽車價格，a_Kia (8500)是第 1 位，a_Toyota (8000)是第 2 位 ，a_Mazda 和 a_Subaru (7000)是第 3 位，a_Honda (6500)是第 5 位，a_Nissan (6200)是第 6 位 ，售出的汽車是馬自達這是第三個所以表格應該如下
ID a_Toyota a_Mazda a_Nissan a_Kia a_Honda a_Subaru SoldCar CarOrder
1 8000 7000 6200 8500 6500 7000 Mazda 3
2 4000 5000 4500 3500 3500 5000 Subaru 1
3 5400 5000 4500 5500 5500 4600 Toyota 3
4 5600 6300 7500 8200 6500 7300 Honda 4
5 8500 7400 7400 6500 9500 9000 Honda 1
6 9900 8000 9900 7300 8100 8000 Honda 3
我可以找到帶有大 CASE 陳述句的訂單
UPDATE mytable
SET CarOrder =
CASE WHEN SoldCar = 'Toyota' AND a_Toyota>=a_Mazda AND a_Toyota>=a_Nissan AND ... AND a_Toyota>=a_Subaru THEN 1
CASE WHEN SoldCar = 'Toyota' AND a_Toyota<a_Mazda AND a_Toyota>=a_Nissan AND ... AND a_Toyota>=a_Subaru THEN 2
CASE WHEN SoldCar = 'Toyota' AND a_Toyota>=a_Mazda AND a_Toyota<a_Nissan AND ... AND a_Toyota>=a_Subaru THEN 2
.
.
.
CASE WHEN SoldCar = 'Toyota' AND a_Toyota>=a_Mazda AND a_Toyota>=a_Nissan AND ... AND a_Toyota<a_Subaru THEN 2
.
.
.
CASE WHEN SoldCar = 'Toyota' AND a_Toyota<a_Mazda AND a_Toyota<a_Nissan AND ... AND a_Toyota>=a_Subaru THEN 3
..
..
..
但這將是一個巨大的案例陳述。
我想知道是否有人有更簡單的方法來做到這一點？
uj5u.com熱心網友回復：
另一種基于 XQuery 的方法。
對于 ID=2 的行，Subary 和 Mazda 之間存在平局。它們的值都為 5000。
查詢陳述句
-- DDL and sample data population, start
DECLARE @tbl TABLE (
ID INT IDENTITY(1,1) PRIMARY KEY,
a_Toyota INT,
a_Mazda INT,
a_Nissan INT,
a_Kia INT,
a_Honda INT,
a_Subaru INT,
SoldCar VARCHAR(20)
);
INSERT INTO @tbl
(
a_Toyota,
a_Mazda,
a_Nissan,
a_Kia,
a_Honda,
a_Subaru,
SoldCar
) VALUES
(8000, 7000, 6200, 8500, 6500, 7000, 'Mazda'),
(4000, 5000, 4500, 3500, 3500, 5000, 'Subaru'),
(5400, 5000, 4500, 5500, 5500, 4600, 'Toyota'),
(5600, 6300, 7500, 8200, 6500, 7300, 'Honda'),
(8500, 7400, 7400, 6500, 9500, 9000, 'Honda'),
(9900, 8000, 9900, 7300, 8100, 8000, 'Honda');
-- DDL and sample data population, end
SELECT t.*, CarOrder
FROM @tbl AS t
CROSS APPLY (SELECT a_Toyota, a_Mazda, a_Nissan,
a_Kia, a_Honda, a_Subaru, SoldCar
FOR XML PATH(''), TYPE, ROOT('root')) AS t1(c)
CROSS APPLY (SELECT c.query('<root>
{
for $r in /root/*
order by data($r) descending
return <r>
<make>{local-name($r)}</make>
<salePrice>{data($r)}</salePrice>
</r>
}
</root>').query('
let $soldcar := sql:column("SoldCar")
for $r in /root/r[contains((make/text())[1], $soldcar)]
let $pos := count(root/*[. << $r])
return $pos').value('.','INT')
) AS t2(CarOrder);
輸出
---- ---------- --------- ---------- ------- --------- ---------- --------- ----------
| ID | a_Toyota | a_Mazda | a_Nissan | a_Kia | a_Honda | a_Subaru | SoldCar | CarOrder |
---- ---------- --------- ---------- ------- --------- ---------- --------- ----------
| 1 | 8000 | 7000 | 6200 | 8500 | 6500 | 7000 | Mazda | 3 |
| 2 | 4000 | 5000 | 4500 | 3500 | 3500 | 5000 | Subaru | 2 |
| 3 | 5400 | 5000 | 4500 | 5500 | 5500 | 4600 | Toyota | 3 |
| 4 | 5600 | 6300 | 7500 | 8200 | 6500 | 7300 | Honda | 4 |
| 5 | 8500 | 7400 | 7400 | 6500 | 9500 | 9000 | Honda | 1 |
| 6 | 9900 | 8000 | 9900 | 7300 | 8100 | 8000 | Honda | 3 |
---- ---------- --------- ---------- ------- --------- ---------- --------- ----------
uj5u.com熱心網友回復：
這是一個選項，您不必列舉要逆透視的列。
這也假設列名的前綴為 a_
示例或
uj5u.com熱心網友回復：
假設一個類似于以下的表結構：
CREATE TABLE tempdb..cars
(
ID INT NOT NULL,
a_Toyota INT NOT NULL,
a_Mazda INT NOT NULL,
a_Nissan INT NOT NULL,
a_Kia INT NOT NULL,
a_Honda INT NOT NULL,
a_Subaru INT NOT NULL,
SoldCar VARCHAR(100) NOT NULL,
CarOrder INT NULL
);
一種方法是利用APPLY 運算子。假設使用 CROSS APPLY 和非密集RANK（與DENSE RANK 相比）以及用于確定排序的降序，類似以下內容應該為您提供上述表結構的結果集：
SELECT c.ID, c.SoldCar, o.ord AS CarOrder
FROM tempdb..cars c
CROSS APPLY
(
SELECT t.ord
FROM (
SELECT r.car, RANK() OVER (ORDER BY r.qty DESC) AS ord
FROM (
SELECT c.a_Toyota AS qty, 'Toyota' AS car
UNION ALL
SELECT c.a_Mazda AS qty, 'Mazda' AS car
UNION ALL
SELECT c.a_Nissan AS qty, 'Nissan' AS car
UNION ALL
SELECT c.a_Kia AS qty, 'Kia' AS car
UNION ALL
SELECT c.a_Honda AS qty, 'Honda' AS car
UNION ALL
SELECT c.a_Subaru AS qty, 'Subaru' AS car
) r
) t
WHERE t.car = c.SoldCar
) o
uj5u.com熱心網友回復：
餐桌結構需要從一開始就調整到它應該的樣子，以便輕松確定正確的訂單并滿足任何數量的品牌的需求。
您可以使用 cross-apply 和 row_number 來匹配每個值與其序號位置，使用可更新的 CTE 來更新基表。
with cars as (
select * from t
cross apply (
select case soldcar
when 'Toyota' then a_Toyota
when 'Mazda' then a_Mazda
when 'Nissan' then a_Nissan
when 'Honda' then a_Honda
when 'Subaru' then a_Subaru
end
)s(SoldValue)
cross apply (
select rank() over (order by v desc) co, v
from (values(a_toyota),(a_mazda),(a_nissan),(a_kia),(a_honda),(a_subaru))v(v)
)c
where SoldValue=v
)
update cars set carOrder=co
見演示小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/398327.html
標籤：sql sql-server 查询语句