SQL按自定义序列排序：实现指定规则的数据集排序

Let's work through getting your dataset sorted exactly as you need it. First, let's recap your source data and desired output to make sure we're aligned:

Source Data #

Desired Sorted Output #

Approach 1: Using a CASE Statement (Simple & Direct) #

This method is perfect if your custom sort order is fixed and doesn't need frequent updates. We'll map each (Id, Value) pair to a specific priority number, then order by that number.

SELECT Id, Value
FROM your_table -- Replace with your actual table name
ORDER BY 
  CASE 
    WHEN Id = 1 AND Value = 'a' THEN 1
    WHEN Id = 2 AND Value = 'a' THEN 2
    WHEN Id = 3 AND Value = 'b' THEN 3
    WHEN Id = 4 AND Value = 'c' THEN 4
    WHEN Id = 1 AND Value = 'b' THEN 5
    WHEN Id = 2 AND Value = 'c' THEN 6
    WHEN Id = 4 AND Value = 'b' THEN 7
    WHEN Id = 1 AND Value = 'c' THEN 8
    WHEN Id = 4 AND Value = 'a' THEN 9
    ELSE 10 -- Any unlisted pairs will be pushed to the end
  END;

Approach 2: Using a CTE for Flexible Sorting #

If you think you might need to adjust the sort order later, or if you have a lot of pairs to define, using a Common Table Expression (CTE) to store the custom sequence is more scalable. We'll create a CTE with the desired sort ranks, then join it to your original table.

WITH custom_sort_order AS (
  SELECT 1 AS sort_rank, 1 AS Id, 'a' AS Value UNION ALL
  SELECT 2, 2, 'a' UNION ALL
  SELECT 3, 3, 'b' UNION ALL
  SELECT 4, 4, 'c' UNION ALL
  SELECT 5, 1, 'b' UNION ALL
  SELECT 6, 2, 'c' UNION ALL
  SELECT 7, 4, 'b' UNION ALL
  SELECT 8, 1, 'c' UNION ALL
  SELECT 9, 4, 'a'
)
SELECT t.Id, t.Value
FROM your_table t
JOIN custom_sort_order co 
  ON t.Id = co.Id AND t.Value = co.Value
ORDER BY co.sort_rank;

Handling Unlisted Pairs

If your original table has (Id, Value) pairs not in your custom order and you want to keep them (instead of filtering them out), use a LEFT JOIN and push those rows to the end:

WITH custom_sort_order AS (
  SELECT 1 AS sort_rank, 1 AS Id, 'a' AS Value UNION ALL
  SELECT 2, 2, 'a' UNION ALL
  SELECT 3, 3, 'b' UNION ALL
  SELECT 4, 4, 'c' UNION ALL
  SELECT 5, 1, 'b' UNION ALL
  SELECT 6, 2, 'c' UNION ALL
  SELECT 7, 4, 'b' UNION ALL
  SELECT 8, 1, 'c' UNION ALL
  SELECT 9, 4, 'a'
)
SELECT t.Id, t.Value
FROM your_table t
LEFT JOIN custom_sort_order co 
  ON t.Id = co.Id AND t.Value = co.Value
ORDER BY COALESCE(co.sort_rank, 999); -- Unlisted pairs get a high rank to go last

Which Approach to Pick? #

• Go with the CASE statement if your sort order is simple and unlikely to change—it's concise and easy to scan.
• Use the CTE method if you expect updates to the sort order, or if you have a large number of pairs to define—it's much easier to maintain over time.

内容的提问来源于stack exchange，提问作者Abdul Mateen