如何编写SQL实现指定行优先及按条件字母排序的查询

Let's work through this SQL problem step by step—starting with the general approach to prioritize specific rows, then moving to your exact business requirement.

General Approach: Push Specific Rows to the Top #

To sort results so that rows matching a specific condition come first, followed by the rest in alphabetical order, you can use a CASE statement in the ORDER BY clause. This creates a "priority flag": matching rows get a lower numeric value (so they sort first), then you layer on your standard alphabetical sort.

For a simple example—say you want all books titled "Dune" at the top, then others sorted by name:

SELECT book_name, book_code
FROM books
ORDER BY
  CASE WHEN book_name = 'Dune' THEN 0 ELSE 1 END,
  book_name ASC;

Tailored SQL for Your Business Scenario #

First, let's lock in logical table relationships based on your description (since you didn't specify exact join keys, I'll use common sense assumptions):

• Table1: Author table, holds Author_code (primary key)
• Table2: Author-book junction table, links Author_code to book_code, plus a flag like is_main_book (boolean to mark the author's primary book)
• Table3: Book table, holds book_code (primary key) and book_name

We'll use a parameter @Author_code to handle both input scenarios (value provided or null):

SELECT 
  t3.book_name,
  t3.book_code
FROM Table3 t3
LEFT JOIN Table2 t2 ON t3.book_code = t2.book_code
LEFT JOIN Table1 t1 ON t2.Author_code = t1.Author_code
ORDER BY
  -- Prioritize the author's main book only when @Author_code is provided
  CASE 
    WHEN @Author_code IS NOT NULL AND t2.Author_code = @Author_code AND t2.is_main_book = 1 THEN 0
    ELSE 1 
  END,
  -- Sort all remaining rows alphabetically by book name
  t3.book_name ASC;

Breakdown of the Logic: #

1. Joins: We connect the book table to the author-book junction table, then to the author table to map books to their respective authors.
2. Sorting:
   • If @Author_code is passed in, the CASE statement assigns a priority of 0 to the author's main book—ensuring it sits at the top. All other rows get a priority of 1.
   • If @Author_code is null, the CASE statement sets every row to priority 1, so the entire result set sorts purely by book_name in alphabetical order.

If your tables use a different way to flag the main book (e.g., a main_book_code column directly in Table1), just adjust the CASE condition to match:

CASE 
  WHEN @Author_code IS NOT NULL AND t3.book_code = t1.main_book_code THEN 0
  ELSE 1 
END

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