WITH CTE AS (
SELECT
column_name,
ROW_NUMBER() OVER (ORDER BY column_name) AS rn,
LAG(column_name) OVER (ORDER BY column_name) AS prev_value,
LEAD(column_name) OVER (ORDER BY column_name) AS next_value
FROM your_table_name
)
SELECT
column_name,
CASE
WHEN column_name = 'z' AND prev_value = 'z' THEN rn + 1
WHEN column_name = 'z' AND next_value = 'z' THEN rn
ELSE rn
END AS new_rank
FROM CTE
ORDER BY new_rank
如果你不是z你就换成数字0
我们使用LAG()和LEAD()函数来获取当前行之前的值和下一行的值。然后,我们使用CASE语句来确定是否需要重新开始计数。如果当前行的值是'z'并且前一行的值也是'z',则将序号增加1。如果当前行的值是'z'并且下一行的值也是'z',则保持当前序号不变。否则,序号保持不变。