mysql SQL查询，用于从Marks表中获取每学期分数不断提高的学生的详细信息

jdzmm42g 于 2023-02-15 发布在 Mysql

关注(0)|答案(2)|浏览(136)

我有一个表标记，我需要打印具有连续增加标记的名称的详细信息。表：标记

Name    Sem Marks
A       1   80
A       2   90
A       3   95
B       1   80
B       2   70
B       3   90
C       1   80
C       2   85
C       3   90

输出：

Name    Sem Marks
A       1   80
A       2   90
A       3   95
C       1   80
C       2   85
C       3   90

有人能给我MySQL查询来打印输出吗
尝试使用分析函数（超前、滞后），但也使用B获取名称。
当前查询

WITH cte AS ( SELECT Name, Sem, Marks, LAG(Marks,1) OVER (PARTITION BY Name ORDER BY Sem) AS Prev_Marks FROM Marks ) SELECT Name, Sem, Marks FROM cte WHERE Marks > Prev_Marks OR Prev_Marks IS NULL ORDER BY Name, Sem;

mysql

来源：https://stackoverflow.com/questions/75447427/sql-query-to-get-the-details-of-students-who-have-continuously-increasing-marks

2条答案

按热度按时间

ttisahbt1#

只需对现有查询稍作修改，就可以使用带有 * not exists * 的关联：

with d as (
  select Name,
  case 
    when Lag(Marks,1) over (partition by Name order by Sem) > Marks then 1
  end Decreasing
 from Marks
)
select Name, Sem, Marks 
from Marks m
where not exists (
  select * from d 
  where d.name = m.name and d.Decreasing = 1
)
order by name, sem;

演示Fiddle

赞(0）回复(0）举报 2023-02-15

8yoxcaq72#

你可以使用rank()来排名分数，然后检查每个级别的排名和Sem是否相等。

WITH cte AS ( 
  SELECT Name, Sem, Marks,
  rank() OVER (PARTITION BY Name ORDER BY Marks) AS rank_mark 
  FROM Marks 
),
cte2 AS (
  SELECT *, if(Sem = rank_mark, 1, 0) as 'isUp'
  FROM cte
)
select t.*
from Marks t
left join (
  select Name
  from cte2 
  where isUp = 0
  group by Name
) as s on s.Name = t.Name 
WHERE s.Name is null;

Demo here

赞(0）回复(0）举报 2023-02-15

我来回答

mysql SQL查询，用于从Marks表中获取每学期分数不断提高的学生的详细信息

2条答案

相关问题

热门标签

最新问答