SQL查询以获取从“Marks”表中每个学期中分数连续增加的学生详细信息。

huangapple go评论63阅读模式
英文:

SQL query to get the details of students who have continuously increasing marks in each semester from the Marks table

问题

以下是您提供的MySQL查询的翻译部分:

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;

请注意,这个查询是用于从名为 "Marks" 的表中打印具有连续增长分数的姓名的详细信息的。

英文:

I am having a table marks, I need to print the details of names having continuously increasing marks.
Table : Marks

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

Output:

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

Can any one give me the MySQL query to print the output

Tried using analytical functions(lead, lag) but getting name with B also.

Current query

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;

答案1

得分: 0

你可以使用 rank() 函数来对成绩进行排名,然后检查每个级别中排名和学期是否相等。

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;

演示链接在这里

英文:

You can do it using rank() to rank Marks then check if the rank and Sem are equal in every level.

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

答案2

得分: 0

使用对现有查询的轻微修改,您可以使用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

英文:

With a slight modification to your existing query you could use a correlation with 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;

Demo Fiddle

huangapple
  • 本文由 发表于 2023年2月14日 19:50:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/75447427.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定