仅打印在近年表现较好的学生姓名
原标题：print only the student names who have performed better in their respective latest years
there is a table
student|Year|Marks
ABC|2023|98
XYZ|2021|100
MNO|2019|81
XYZ|2020|98
MNO|2018|98
ABC|2022|100
the result should just show
XYZ|2021|100
i am trying to create the query as
with cte as
(select *, row_number() over (partition by name, year order by marks) as rn
from table1)
select name, year, marks
from CTE
where rn = 1
the condition which i am unable to include is the comparison of the marks in between the 2 years data.
问题回答
One option could be using LAG() Over() analytic function to get last year s marks in the same row allowing you to filter the data for any condition you need (marks better, worse, equal ...)
With -- S a m p l e D a t a:
tbl ( student, a_year, marks ) AS
( Select ABC , 2023, 98 Union All
Select XYZ , 2021, 100 Union All
Select MNO , 2019, 81 Union All
Select XYZ , 2020, 98 Union All
Select MNO , 2018, 98 Union All
Select ABC , 2022, 100
)
S Q L :
SELECT t.*
FROM ( Select student, a_year, marks,
LAG(marks)
Over( Partition By student Order By a_year ) as last_year_marks
From tbl
) t
ORDER BY t.student, t.a_year Desc;
/* R e s u l t :
student a_year marks last_year_marks
------- ------ ----- ---------------
ABC 2023 98 100
ABC 2022 100 null
MNO 2019 81 98
MNO 2018 98 null
XYZ 2021 100 98
XYZ 2020 98 null */
... so, if you add Where clause to the outer query you could get the expected result ...
S Q L :
SELECT t.*
FROM ( Select student, a_year, marks,
LAG(marks)
Over( Partition By student Order By a_year ) as last_year_marks
From tbl
) t
WHERE t.marks > t.last_year_marks
ORDER BY t.student, t.a_year Desc;
/* R e s u l t :
student a_year marks last_year_marks
------- ------ ----- ---------------
XYZ 2021 100 98 */
You can use NOT EXISTS to return each student s last year result as long as it has the student s highest marks.
select t.*
from table1 t
where not exists (select * from table1 t2
where t2.student = t.student
and (t2.year > t.year or t2.marks > t.marks));
MySQL users might want to do a self LEFT JOIN instead:
select t.*
from table1 t
left join table1 t2
on t2.student = t.student
and (t2.year > t.year or t2.marks > t.marks)
where t2.student is null;
MySQL version 8 users can use row_number() window function, partition by student, to make sure the latest year also has the highest marks:
select student, year, marks
from
(
select student, year, marks,
row_number() over (partition by student order by marks desc) rn_m,
row_number() over (partition by student order by year desc) rn_y
from table1
) dt
where rn_m = 1 and rn_y = 1;
Demo: https://dbfiddle.uk/vyNa1iq7 (based on @nbk s fiddle, thanks!)
相关问题
SQL SubQuery getting particular column
I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question:
The subquery below returns a Table with 3 ...
please can anyone check this while loop and if condition
<?php
$con=mysql_connect("localhost","mts","mts");
if(!con)
{
die( unable to connect . mysql_error());
}
mysql_select_db("mts",$con);
/*
date_default_timezone_set ("Asia/Calcutta");
$date = ...
Zend Framework: Authentication, MySQL在从Qalite转变时的错误
我把我的用心从使用QQL转向MySQL。 它与凯科特合作,现在不工作,因为我已经改变,使用MySQL。 这里的错误信息是:
SQL Query for Performing Radius Search based on Latitude Longitude
We have a restaurant table that has lat-long data for each row.
We need to write a query that performs a search to find all restaurants within the provided radius e.g. 1 mile, 5 miles etc.
We have ...
php return a specific row from query
Is it possible in php to return a specific row of data from a mysql query?
None of the fetch statements that I ve found return a 2 dimensional array to access specific rows.
I want to be able to ...
Character Encodings in PHP and MySQL
Our website was developed with a meta tag set to...
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
This works fine for M-dashes and special quotes, etc. However, I ...
Pagination Strategies for Complex (slow) Datasets
What are some of the strategies being used for pagination of data sets that involve complex queries? count(*) takes ~1.5 sec so we don t want to hit the DB for every page view. Currently there are ~...
Averaging a total in mySQL
My table looks like
person_id | car_id | miles
------------------------------
1 | 1 | 100
1 | 2 | 200
2 | 3 | 1000
2 | 4 | 500
I need to ...
