sql-order-by - 如何使用条件联合同一张表中的两个有序查询

Question

我有一张这样的表： x asc 给出的命令：

'Id', 'X', 'TYPE', 'ULLiID', 'ULLiText'
(1, '16', 'UL', 'tabcde1','ULText1'),
(5, '17', 'LI', 'lkjhg1',''),
(6, '17', 'LI', 'yuiop1',''),
(8, '25', 'LI', 'abcde1',''),
(4, '26', 'UL', 'tlkjhg1','ULText2'),
(3, '27', 'UL', 'tyuiop1','ULText3'),    
(9, '30', 'LI', 'lkjhg2',''),
(7, '31', 'UL', 'tessss','ULno'),
(2, '32', 'LI', 'wwxxxx',''),

TYPE DESC, RIGHT(ULLiID, 5) ASC 的订单给出：

(1, '16', 'UL', 'tabcde','ULText1'),
(8, '25', 'LI', 'abcde',''),
(4, '26', 'UL', 'tlkjhg1','ULText2'),
(5, '17', 'LI', 'lkjhg1',''),
(9, '30', 'LI', 'lkjhg2',''),
(3, '27', 'UL', 'tyuiop1','ULText3'),
(6, '17', 'LI', 'yuiop1',''),
(2, '32', 'LI', 'wwxxxx',''),
(7, '31', 'UL', 'tessss','ULno')

我需要它

(1, '16', 'UL', 'tabcde','ULText1'),
(8, '25', 'LI', 'abcde',''),
(4, '26', 'UL', 'tlkjhg1','ULText2'),
(5, '17', 'LI', 'lkjhg1',''),
(9, '30', 'LI', 'lkjhg2',''),
(3, '27', 'UL', 'tyuiop1','ULText3'),
(6, '17', 'LI', 'yuiop1',''),
(7, '31', 'UL', 'tessss','ULno'),
(2, '32', 'LI', 'wwxxxx','')

这是我到目前为止所做的：

WITH CTE AS
(
SELECT  * ROW_NUMBER() OVER(ORDER BY X ASC) AS num_row
            FROM table
            ORDER BY X ASC
),
CTE2 AS
(
SELECT
    *
            ROW_NUMBER() OVER(ORDER BY TYPE DESC, RIGHT(ULLiID, 5) ASC) AS num_row2
            FROM table
            ORDER BY TYPE DESC, RIGHT(ULLiID, 5) ASC
),CTE3 AS
(
   SELECT * from CTE c1 where  TYPE='UL' AND (num_row in(select num_row-1 from CTE where TYPE='UL'))
   
),CTE4 AS
(
    SELECT * FROM CTE3
UNION DISTINCT
SELECT * FROM  CTE2 c2 WHERE num_row2 BETWEEN 1 AND type='UL'
)

从 CTE3 中选择 * 给出：

(4, '26', 'UL', 'tlkjhg1','ULText2'),

然后我尝试使用 CTE4

(4, '26', 'UL', 'tlkjhg1','ULText2'),
(5, '17', 'LI', 'lkjhg1',''),
(9, '30', 'LI', 'lkjhg2','')

将其与 CTE 结合并提供我需要的东西

标签： sql-order-byunionbetweenrow-number