问题

https://stackoverflow.com/questions/70512837/is-there-a-faster-way-to-find-the-order-of-a-column

My SQL Server table looks like this

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

I want to Update CarOrder field, so it has the order of price of the sold car compare to other car prices.

So for ID 1 car prices ordered asa_Kia (8500)is1standa_Toyota (8000)is2ndanda_Mazda & a_Subaru (7000)are3rdanda_Honda (6500)is5thanda_Nissan (6200)is6thand the sold car was Mazda which is 3rd so the table should be as follow

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

I can find the order with a large CASE statement

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

..

..

..

but this is going to be a huge case statement.

I wonder if someone has an easier way to do that?

解答

这问题非常简单，在每条记录后追加当前SoldCar在前6列的排名即可。这类运算用SQL写起来很麻烦，需要cross apply配合XQuery，或者cross apply配合OpenJson与窗口函数才能完成，写出的SQL冗长难读。通常的办法是读出来用Python或SPL来做， SPL（一种 Java 的开源包）更容易被Java应用集成，代码也更简单一点，只要三句：

SPL源代码：https://github.com/SPLWare/esProc

问答搜集