The problem of student ranking in SQL

--constraints on the input data
CREATE TABLE T (StudentID char (ten), [name] varchar (8),
StartDate Char Check (isdate (startdate) =1 and cast (StartDate as DateTime) =convert (datetime,startdate,120))
INSERT INTO T
Select ' AA ', ' BB ', ' 77799820 '
UNION ALL select ' BB ', ' ggg ', ' 2007-10-11 '
UNION ALL SELECT ' CC ', ' ddd ', ' 2007-20-11 '
SELECT * FROM t
--Create a test table
CREATE table TBL ([name] varchar (8), Chinese float, math float,zong float,px int)
Insert TBL
Select ' A ', 90,null,null,
UNION ALL SELECT ' B ', 88,null,null
UNION ALL SELECT ' C ', 93,null,null
UNION ALL SELECT ' d ', 90,null,null
UNION ALL SELECT ' E ', 100,null,null
--Another approach
Select *,zong=chinese + Math,
px= (select COUNT (1) +1 from TBL B where B.chinese + B.Math >a.chinese + a.math)
From TBL a order by Px,chinese

--Create a stored procedure
create proc MySQL
As
Update BB set zong=tt.zong,px=tt.px from TBL BB,
(select Name,zong, px= (select COUNT (Zong) from
(
Select Name,chinese,math,zong = chinese + math from tbl
) T
WHERE zong > A.zong or (zong = A.zong and Chinese > A.chinese)) + 1
From
(
Select Name,chinese,math,zong = chinese + math from tbl
) A
) TT
where Bb.name=tt.name
Go
--Call the stored procedure
exec MySQL
--View
SELECT * FROM TBL

--Delete
drop table TBL
drop proc MySQL

--Create a table
CREATE TABLE TB (ID int,team varchar (ten), score int,score2 int,score3 int)
INSERT into TB values (1, ' A ', 100, 100, 100)
INSERT into TB values (2, ' A ', 90, 100, 100)
INSERT into TB values (3, ' B ', 50, 40, 30)
INSERT into TB values (4, ' B ', 50, 40, 30)
INSERT into TB values (5, ' A ', 90, 100, 80)
INSERT into TB values (6, ' B ', 55, 50, 50)
INSERT into TB values (7, ' A ', 90, 100, 50)

--Query
Select *,px= (select COUNT (1) +1 from TB
where Team=a.team and (score >a.score)
or (score = A.score and Score2 >a.score2)
or (score = A.score and Score2=a.score2 and Score3 >a.score3)
or (score = A.score and Score2=a.score2 and score3=a.score3 and ID <a.id))
From TB a order by team

--Delete
DROP table TB

The problem of student ranking in SQL