sql - 从sql server中的表中获取前n行

Question

所以让我先粘贴两个表架构

CREATE TABLE Segment_Master ( [segment_id] bigint, [subject_code_id] bigint, [segment_name] nvarchar(60), [segment_description] nvarchar(250), [must_attend_question] tinyint, [total_question] tinyint, [branch_id] bigint, [entry_by] bigint, [entry_date] datetime, [test_id] int, [neg_marks_each_quest] decimal(4,2) )
INSERT INTO Segment_Master
VALUES
( 1, 1, N'First Segment', N'First Segment Description', 5, 5, 15, 10238, N'2018-05-16T13:03:17.583', 1, 0.50 ), 
( 2, 1, N'Second Section', N'Second Segment', 5, 6, 15, 10238, N'2018-05-16T13:03:17.583', 1, 0.00 ), 
( 3, 1, N'Third Segment', N'Third Segment', 1, 2, 15, 10238, N'2018-05-16T13:03:17.583', 1, 0.00 )

现在让我显示第二个表..

CREATE TABLE OnlineTestAnswer ( [auto_id] bigint, [segment_id] int, [question_id] int, [marks] decimal(9,4), [student_id] int, [test_id] int, [branch_id] int, [faculty_id] int )
INSERT INTO OnlineTestAnswer
VALUES
( 1, 1, 1, 1.0000, 10246, 1, 15, 10246 ), 
( 2, 1, 31, -0.5000, 10246, 1, 15, 10246 ), 
( 3, 1, 32, -0.5000, 10246, 1, 15, 10246 ), 
( 4, 1, 33, -0.5000, 10246, 1, 15, 10246 ), 
( 5, 1, 34, 0.3700, 10246, 1, 15, 10246 ), 
( 6, 2, 2, 0.0000, 10246, 1, 15, 10246 ), 
( 7, 2, 8, 2.0000, 10246, 1, 15, 10246 ), 
( 8, 2, 31, 0.0000, 10246, 1, 15, 10246 ), 
( 9, 2, 35, 1.0000, 10246, 1, 15, 10246 ), 
( 10, 2, 21, 2.0000, 10246, 1, 15, 10246 ), 
( 11, 2, 22, 2.0000, 10246, 1, 15, 10246 ), 
( 12, 3, 15, 3.5000, 10246, 1, 15, 10246 )

现在，如果我在这些表上运行选择查询..

现在，如果你不用放大镜也能看到的话:D，有两列:Must_attend_question 和total_question 顾名思义must_attend_question<=total_question现在让我运行另一个表，

现在故事来了，假设A是一个段，它有5个号。其中 4 个问题为必答题。

现在，如果我给出了全部 5 个答案，那么必须计算前 4 个答案。即如果你看第二张图片，最大数量。第 2 部分的分数将是7. (2+2+2+1+0) 注意。这里将计算前 5 名..

我想要输出这两个图像，

segment_id|marks
----------|------ 
    1     | -.13
----------|------
   2      |  7
----------|-----
   3      |  3.5

Answer 1

您可以使用CROSS APPLY动态获取前N，同时按标记排序，然后按每个段进行分组。

SELECT
    T.segment_id,
    SumTopMarks = SUM(S.marks)
FROM
    Segment_Master AS T
    CROSS APPLY (
        SELECT TOP (T.must_attend_question)
            O.*
        FROM
            OnlineTestAnswer AS O
        WHERE
            T.segment_id = O.segment_id
        ORDER BY
            O.marks DESC
    ) AS S
GROUP BY
    T.segment_id

如果您的 Segment_Master 没有答案，而您希望将其列出，则可以将 CROSS APPLY 更改为 OUTER APPLY。