如何翻转两行值?

Question

ID  TYPE      ORDER_INDEX  CITY_ID
-----------------------------------
1   CAT            1          1
2   DOG            2          1
3   CAT            4          2 
4   DOG            5          2
5   BEE            9          1

对于每个城市，我需要交换cat和dog中的order_index。

所以ID为1的城市应该有猫=2狗=1,ID为2的城市应该有猫=5狗=4。

如何用纯SQL完成?

Answer 1

使用更新:

UPDATE yourTable t1
SET ORDER_INDEX = (SELECT t2.ORDER_INDEX FROM yourTable t2
WHERE t2.CITY_ID = t1.CITY_ID AND
t2.TYPE IN ('CAT', 'DOG') AND
t2.TYPE <> T1.TYPE)
WHERE TYPE IN ('CAT', 'DOG');

上面的更新逻辑假设您希望在具有相同CITY_ID的狗/猫对记录之间进行ORDER_INDEX交换。

Answer 2

好吧，如果你不关心ID，那么有一个简单的选项可以做你想要的:

:

SQL> select * from test order by id;
ID TYP ORDER_INDEX    CITY_ID
---------- --- ----------- ----------
1 CAT           1          1
2 DOG           2          1
3 CAT           4          2
4 DOG           5          2
5 BEE           9          1

更新:

SQL> update test set
2    type = decode(type, 'DOG', 'CAT', 'DOG')
3  where type in ('CAT', 'DOG');
4 rows updated.

后;要求是

ID为1的城市应该有猫=2和狗=1,ID为2的城市应该有猫=5和狗=4。

SQL> select * from test order by id;
ID TYP ORDER_INDEX    CITY_ID
---------- --- ----------- ----------
1 DOG           1          1  --> city with ID = 1 has DOG = 1 ...
2 CAT           2          1  --> ... and CAT = 2
3 DOG           4          2  --> city with ID = 2 has DOG = 4 ...
4 CAT           5          2  --> ... and CAT = 5
5 BEE           9          1
SQL>