例如，我有一個表：埠表
| 否 | 一個埠 | B口 |
|---|---|---|
| 1 | 80 | 100 |
| 2 | 90 | 110 |
| 3 | 100 | 80 |
| 4 | 94 | 106 |
我想洗掉記錄號。3，因為它與記錄號具有相同的組合。1 如何在oracle中做到這一點？
uj5u.com熱心網友回復：
您可以使用單個MERGE陳述句和與andROW_NUMBER結合使用的分析函式來查找和洗掉重復項：GREATESTLEAST
MERGE INTO table_name dst
USING (
SELECT ROWID rid,
ROW_NUMBER() OVER (
PARTITION BY LEAST(A_Port, B_Port), GREATEST(A_Port, B_Port)
ORDER BY S_no
) AS rn
FROM table_name
) src
ON (dst.ROWID = src.rid AND src.rn > 1)
WHEN MATCHED THEN
UPDATE SET A_port = NULL
DELETE WHERE 1 = 1;
其中，對于您的示例資料：
CREATE TABLE table_name (S_No, A_Port, B_port) AS
SELECT 1, 80, 100 FROM DUAL UNION ALL
SELECT 2, 90, 110 FROM DUAL UNION ALL
SELECT 3, 100, 80 FROM DUAL UNION ALL
SELECT 4, 94, 106 FROM DUAL;
將洗掉第 3 行。
db<>在這里擺弄
uj5u.com熱心網友回復：
在mysql中試過這個，做一些測驗/場景
SELECT P1.* FROM port_tbl AS P1
LEFT JOIN port_tbl AS P2 ON P1.port1 = P2.port2 OR P1.port2 = P2.port1
WHERE P1.id < P2.id OR ISNULL(P2.id)
ORDER BY P1.id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/472535.html