英文:
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;
答案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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论