用于计算样本的最大值、最小值和模式的SQL查询。

I have a table for marks of students as follows:

我有一张学生的分数表:

SrNo   Class    Name    Marks
1      1A     Student1  67
2      1A     Student2  62
3      1A     Student3  65
4      1A     Student4  78
5      1A     Student5  28
6      1B     Student6  57
7      1B     Student7  65
8      1B     Student8  85
9      1B     Student9  18
10     1B     Student10  8

I want the results as 3 rows from each class with highest, lowest and average marks.

我希望结果是每类中有最高、最低和平均分数的3行。

The result would ideally be:

理想的结果是:

SrNo Class Student Marks 
4     1A    Student4 78
5     1A    Student5 28
2     1A    Student2 62
8     1B    Student8 85
10    1B    Student10 8
6     1B    Student6 57

3 个解决方案

#1

You can try something like this to achieve it:

你可以尝试以下方法来实现:

-- Create demo data
CREATE TABLE #temp(SrNo int, Class nvarchar(5), Name nvarchar(50), Marks int)

INSERT INTO #temp(SrNo, Class, Name, Marks)
VALUES  (1,'1A','Student1',67),
        (2,'1A','Student2',62),
        (3,'1A','Student3',65),
        (4,'1A','Student4',78),
        (5,'1A','Student5',28),
        (6,'1B','Student6',57),
        (7,'1B','Student7',65),
        (8,'1B','Student8',85),
        (9,'1B','Student9',18),
        (10,'1B','Student10',8)

-- your part
SELECT t.*
FROM (
    SELECT  Class,
            MIN(Marks) as min_marks,
            AVG(Marks) as avg_marks,
            MAX(Marks) as max_marks
    FROM #temp 
    GROUP BY class
) as data
OUTER APPLY (
    SELECT TOP 1 t.marks as nearest_avg
    FROM #temp as t
    WHERE t.class = data.Class
    ORDER BY CASE WHEN data.avg_marks-marks >= 0 THEN data.avg_marks-Marks ELSE Marks-data.avg_marks END
) as avg_data
INNER JOIN #temp as t
        ON t.Class = data.Class
        AND( 
            t.Marks = data.min_marks
            OR t.marks = avg_data.nearest_avg
            OR t.marks = data.max_marks
        )

-- Cleanup
DROP TABLE #temp

#2

You can use a combination of ROW_NUMBER and Aggregate functions with OVER like this.

可以像这样使用ROW_NUMBER和聚合函数的组合。

ROW_NUMBER() OVER(PARTITION BY Class ORDER BY ABS(Marks - AvgMarks)) gets the a Student with the marks closest to the average of the class.

ROW_NUMBER()除以(按类的顺序除以ABS(Marks - AvgMarks))得到的a学生的分数与班级的平均分数最接近。

SQL Fiddle

SQL小提琴

Query

查询

;WITH CTE AS 
(
SELECT
    MAX(Marks)OVER(PARTITION BY Class) MaxMarks,
    MIN(Marks)OVER(PARTITION BY Class) MinMarks,
    AVG(Marks)OVER(PARTITION BY Class) AvgMarks,
    [SrNo], [Class], [Name], [Marks]
FROM Class
), CTEAvg as 
(
  SELECT [SrNo], [Class], [Name], [Marks],MaxMarks,MinMarks,
         ROW_NUMBER() OVER(PARTITION BY Class ORDER BY ABS(Marks - AvgMarks)) ClosestAvg
  FROM CTE
)
SELECT [SrNo], [Class], [Name], [Marks]
FROM CTEAvg
WHERE [Marks] = MaxMarks
    OR [Marks] = MinMarks
    OR ClosestAvg = 1;

Output

输出

| SrNo | Class |      Name | Marks |
|------|-------|-----------|-------|
|    2 |    1A |  Student2 |    62 |
|    4 |    1A |  Student4 |    78 |
|    5 |    1A |  Student5 |    28 |
|    6 |    1B |  Student6 |    57 |
|   10 |    1B | Student10 |     8 |
|    8 |    1B |  Student8 |    85 |

#3

This is for Highest / Lowest marks class wise,

这是最高/最低的分数，

Select Class, Student, Marks from
(
    Select row_number() over (order by class, marks desc) as row_id,
        class,
        student,
        marks
    from student
)T
where row_id = 1 or row_id = 
(
    select max(row_id) from t
)

#1