Add an ordinal column to the query results in MySQL

　　Health into a field (not a table field) to record the sort "analogy is rownum in an Oracle database"

One table in MySQL (table name: Stuscore)

Fields are: ID (primary key), Stuid (school number), sname (student name), subject (course name), score (score)

　　　　

List Math Scores (Required fields: rank, name, course name, score, study number)

The field used for ranking, which is represented here as a pm when queried

　　　　

　　　　 Select (@i:[email protected]+1)pm,s.* from Stuscore S, (select @i:=0) t where s.subject= ' math '

ORDER BY score Desc;

Thought: Define a variable in advance, through the recursion of the variable and the virtual table to achieve the purpose of generating serial number.

When the SELECT * from Table1,table2 does not add any conditions, the data of the two tables is displayed after the Cartesian product,

As in SQL, this table is actually a i=0 of data.

　　　　There are two ways to define variables in a MySQL stored procedure:

1. Use set or select to assign a value directly, the variable name begins with @

　　　　　　Assignment symbols: You can use set with "=" or ": =" But you must use ": =" To assign values when using select

For example: [email protected]=1;

You can declare anywhere in a session that the scope is the entire session, called the session variable.

The MySQL total variable does not have to declare beforehand, uses at the time directly with the @ variable name to use

　　　"Advance Record"

　　　　2 , variables declared with the DECLARE keyword, which can only be used in stored procedures, are called stored procedure variables, for example:
DECLARE var1 INT DEFAULT 0;

is used primarily in stored procedures, or in storage-pass parameters.

The difference between the two is:

Variables declared with declare are initialized to NULL when the stored procedure is called. The session variable (that is, the variable at the start of @)

is not reinitialized, in a session, it is initialized only once, and then within the session is the result of the last calculation, which is equivalent to

is a global variable within this session.

In a stored procedure, dynamic content must be assigned to a session variable when it is preprocessed using dynamic statements.
Cases:
Set @v_sql = SQLText;
PREPARE stmt from @v_sql;
EXECUTE stmt;
Deallocate PREPARE stmt;

　　

Add an ordinal column to the query results in MySQL