每种记录类型的SQL Server示例

I have a table full of records for various semesters, and I want a query to select the top 5 rows from each semester.

我有一个满是不同学期记录的表格，我想要一个查询来从每个学期中选择前5行。

I'd like one resultset sorted by a key field (serial) and one resultset with random sorts within each semester.

我希望每个学期都有一个按关键字段(串行)排序的结果集和一个随机排序的结果集。

The table looks like this:

表格是这样的:

• serial int
• 连续的整数
• semester char(4)
• 学期char(4)
• grade int
• 年级int

Table data:

表数据:

Semester|Serial|NewSerial
SP10    |1     |1001
SP10    |2     |1002
SP10    |3     |1003
SP10    |4     |1004
SP10    |5     |1005
SP10    |6     |1006
SP10    |7     |1007
SP10    |8     |1008
SP10    |9     |1009
SP10    |10    |1010
FA10    |1     |2001
FA10    |2     |2002
FA10    |3     |2003
FA10    |4     |2004
FA10    |5     |2005
FA10    |6     |2006
FA10    |7     |2007
FA10    |8     |2008
FA10    |9     |2009
FA10    |10    |2010
FA09    |1     |3001
FA09    |2     |3002
FA09    |3     |3003
FA09    |4     |3004
FA09    |5     |3005
FA09    |6     |3006
FA09    |7     |3007
FA09    |8     |3008
FA09    |9     |3009
FA09    |10    |3010

Result 1: ordered by serial, top 5

结果1:按系列排序，排名前5

Semester|Serial|NewSerial
SP10    |1     |1001
SP10    |2     |1002
SP10    |3     |1003
SP10    |4     |1004
SP10    |5     |1005
FA10    |1     |2001
FA10    |2     |2002
FA10    |3     |2003
FA10    |4     |2004
FA10    |5     |2005
FA09    |1     |3001
FA09    |2     |3002
FA09    |3     |3003
FA09    |4     |3004
FA09    |5     |3005

Result 2: order by random within semester (sampling)

结果2:学期内随机排序(抽样)

Semester|Serial|NewSerial
SP10    |3     |1003
SP10    |1     |1001
SP10    |5     |1005
SP10    |2     |1002
SP10    |4     |1004
FA10    |2     |2002
FA10    |1     |2001
FA10    |4     |2004
FA10    |3     |2003
FA10    |5     |2005
FA09    |3     |3003
FA09    |1     |3001
FA09    |2     |3002
FA09    |5     |3005
FA09    |4     |3004

3 个解决方案

#1

these should do it:

这些应该这样做:

WITH [query_semester] AS (
    SELECT RN = ROW_NUMBER() OVER (PARTITION BY semester
    ORDER BY serial),
    serial, semester, grade
    FROM SemesterTable)
SELECT serial, semester, grade
FROM [query_semester] WHERE [RN] <= 5;

and the random one:

随机一个:

WITH [query_semester] AS (
    SELECT RN = ROW_NUMBER() OVER (PARTITION BY semester
    ORDER BY NEWID()),
    serial, semester, grade
    FROM SemesterTable)
SELECT serial, semester, grade
FROM [query_semester] WHERE [RN] <= 5;

#2

Show us some sample data, and expected results.

向我们展示一些示例数据和预期结果。

You will need to SELECT TOP 5 You will also need to GROUP BY the semester type (if that is a date so be it).

你需要选择前五名，你还需要按学期类型分组(如果那是一个日期，那就随它去吧)。

Instead of TOP 5 use HAVING count...

用count代替TOP 5…

#3

No idea what criteria you are using to make up the new serial, but if it just needs to be serially incrementing by semester (in the thousand position), then

不知道你用什么标准来组成新的系列，但是如果它只是需要按学期顺序递增(在千位)，那么

select semester, serial,
    newserial = (DENSE_RANK() over (order by semester) * 1000) + rn
from
(   select *, rn=ROW_NUMBER() over (partition by semester order by serial)
    from tbl
) x
where rn <= 5
order by semester

For the order by serial. Swap order by serial for order by newid() to get the random sampling.

按顺序排列。newid()通过串行交换订单来获得随机抽样。

Notes:

注:

1. SQL Server 2005+ at compatibility level 90+
2. SQL Server 2005+兼容性级别90+
3. The formula for newserial works fine because each semester can only have 5 items so there won't be *ing issues
4. newserial这个公式很好用，因为每个学期只能有5个条目，所以不会有冲突问题

EDIT

It seems counter-intuitive in the question, but if newserial is just a regular field (it wasn't listed), then the select part becomes simply

这个问题似乎与直觉相反，但是如果newserial只是一个常规字段(它没有列出)，那么select部分就变得简单了

select semester, serial, newserial