SQL |基於另一張表的 row_number 設置的 Shuffle 順序

我需要在不同表中提到的基於表的順序中手動改組由 row_number 設置的順序 -

表 A

表 B

使用表 B 的預期結果

我正在嘗試使用以下查詢，但它不能完全工作；敲我的頭看看我錯過了什麼：O

with t1 as (select name,
                  ROW_NUMBER() OVER (ORDER BY name asc) as default_order_no
           from A),
    t2 as (
        select t1.*,
               ROW_NUMBER() OVER (ORDER BY coalesce(B.manual_order_no, default_order_no)) as order_no
        from B
                 right join t1 on B.name = t1.name)
select t2.name, row_number() OVER (ORDER BY order_no asc) as final_order_no
from t2

WITH RECURSIVE
nums AS ( SELECT 1 manual_order_no
         UNION ALL
         SELECT manual_order_no + 1 
         FROM nums 
         WHERE manual_order_no < ( SELECT COUNT(*) 
                                   FROM A )
),
A1 AS ( 
   SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn
   FROM A
   LEFT JOIN B USING (name)
   WHERE B.manual_order_no IS NULL
), 
nums1 AS (
   SELECT manual_order_no, ROW_NUMBER() OVER (ORDER BY name) rn
   FROM nums
   LEFT JOIN B USING (manual_order_no)
   WHERE B.manual_order_no IS NULL
)
SELECT name, manual_order_no
FROM B
UNION ALL
SELECT A1.name, nums1.manual_order_no
FROM A1
JOIN nums1 USING (rn)
ORDER BY manual_order_no

和

INSERT INTO B (name, manual_order_no)
WITH RECURSIVE
nums AS ( SELECT 1 manual_order_no
         UNION ALL
         SELECT manual_order_no + 1 
         FROM nums 
         WHERE manual_order_no < ( SELECT COUNT(*) 
                                   FROM A )
),
A1 AS ( 
   SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn
   FROM A
   LEFT JOIN B USING (name)
   WHERE B.manual_order_no IS NULL
), 
nums1 AS (
   SELECT manual_order_no, ROW_NUMBER() OVER (ORDER BY name) rn
   FROM nums
   LEFT JOIN B USING (manual_order_no)
   WHERE B.manual_order_no IS NULL
)
SELECT A1.name, nums1.manual_order_no
FROM A1
JOIN nums1 USING (rn)

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a767751d4f21961ba3946a457f5baa40

引用自：https://dba.stackexchange.com/questions/308780