Now we need to implement such a function: sort the height of children of the corresponding age in the database, and get the height ranking of the child with the specified ID. For example, you want to query the height ranking of the 8-year-old child with ID 9527 among the heights of all 8-year-old children. I don’t know much about databases, so I usually just add, delete, modify and delete, so I don’t know how to implement this function.
I queried some information online. I sorted it first, generated the row serial number, and then returned the serial number corresponding to the child ID. There is a similar statement as follows
select id,height,(@rowno:=@rowno+1) as rowno from test,(select (@rowno:=0)) b
order by height desc;
Based on this, query the location:
select rowno from (select id,height,(@rowno:=@rowno+1) as rowno from test,
(select (@rowno:=0)) b order by height desc) c where id = 9527;My question is:
1. What are (@rowno:=@rowno+1), (select (@rowno:=0)), b, and C in the statement? I looked it up and found out that it is something like custom named assignment, but I don’t know exactly how to understand it. What do the b and c that appear mean?
2. I want to add a condition to this statement, which is the height ranking of eight-year-old children. That is, I need to add where age equals 8 to the first sorting statement. Where should this sentence be added? I tried adding it after from test and in several other places, but I got errors. I don’t know how to add it.
I have been troubled for a day. I hope the database prawns can help answer these two questions. Thank you very much! ! !
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
1, get the height of 9527 as h
2, select count(0) from tb where age = 8 and height > h;
3, then the height ranking of 9527 is tied count + 1,
It doesn’t need to be so complicated, height ranking, if you rank from high to low, calculate it like this:
a=height field
select count(*) from children where
age=8 and
a>=(select a from children where id=9527 );
count(*) is the ranking
mysql does not support rank functions... In relatively powerful databases such as oracle, postgres, etc., it can be easily implemented using row_number()
If the column of the database is id, age, height
select id, age, height,
row_number() over (partition by age order by height desc) as rn
from tb