SQL Server：如何在SELECT查询中按优先级筛选获取指定学生数据

Got it, let's work through this to get exactly the 10000 student records you need, following your priority rules and sorting requirement.

Core SQL Query (MySQL/PostgreSQL) #

SELECT *
FROM (
    -- Grab all Active=1 students first (our top priority)
    SELECT *, 1 AS priority
    FROM student
    WHERE Active = 1
    UNION ALL
    -- Fill the rest with Nationality=1 students who aren't already in the Active group
    SELECT *, 2 AS priority
    FROM student
    WHERE Nationality = 1 AND Active != 1
) combined_results
ORDER BY priority, admissionDate
LIMIT 10000;

How This Works #

Let me break down the logic so you can follow along:

• First subquery: We select every student where Active = 1 and tag them with a priority value of 1. This ensures these records are prioritized over the补充 ones when we sort and limit results.
• Second subquery: We pull in students where Nationality = 1 but exclude those who are already in the Active group (using Active != 1) to avoid duplicates. These get a priority of 2 since they're our backup.
• Outer query: We combine both datasets, sort first by priority (so Active students stay at the top and aren't cut off by the limit) then by admissionDate as requested, finally grabbing the first 10000 records.

Adjustments for Other Databases #

If you're using a different SQL dialect, here's how to tweak the query:

• SQL Server: Replace LIMIT 10000 with TOP 10000 in the outer select:

  SELECT TOP 10000 *
  FROM (
      SELECT *, 1 AS priority
      FROM student
      WHERE Active = 1
      UNION ALL
      SELECT *, 2 AS priority
      FROM student
      WHERE Nationality = 1 AND Active != 1
  ) combined_results
  ORDER BY priority, admissionDate;
  

• Oracle: Use ROWNUM to limit results (needs an extra nested select for proper sorting):

  SELECT *
  FROM (
      SELECT *
      FROM (
          SELECT *, 1 AS priority
          FROM student
          WHERE Active = 1
          UNION ALL
          SELECT *, 2 AS priority
          FROM student
          WHERE Nationality = 1 AND Active != 1
      ) combined_results
      ORDER BY priority, admissionDate
  )
  WHERE ROWNUM <= 10000;
  

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