MySQL line number problem

Time:2022-1-15

1. Line number problem

Row numbers are consecutive integers that are sequentially assigned to the rows of the query result set. The line number support of MySQL database is not very friendly. It does not provide a similar row as other databases_ Number solution, so getting the line number is a very tricky problem.

2.1 analysis of non duplicate data

First look at the following example data and create the sales table:

CREATE TABLE sales
(
empid VARCHAR(10) NOT NULL,
mgrid VARCHAR(10) NOT NULL,
qty INT NOT NULL,
PRIMARY KEY (empid)
);
Insert instance data:
INSERT INTO sales VALUES(‘A’,’Z’,300);
INSERT INTO sales VALUES(‘B’,’X’,100);
INSERT INTO sales VALUES(‘C’,’X’,200);
INSERT INTO sales VALUES(‘D’,’Y’,200);
INSERT INTO sales VALUES(‘E’,’Z’,250);
INSERT INTO sales VALUES(‘F’,’Z’,300);
INSERT INTO sales VALUES(‘G’,’X’,100);
INSERT INTO sales VALUES(‘H’,’Y’,150);
INSERT INTO sales VALUES(‘I’,’X’,250);
INSERT INTO sales VALUES(‘J’,’Z’,100);
INSERT INTO sales VALUES(‘K’,’Y’,200);
To view sales data:
select * from sales;
MySQL line number problem
The analysis problem is how to use SQL statements to output empid with a line number? Start with 1? The sales table empid is the primary key and there will be no duplicate data. If there is no duplicate data in the primary key, how to write SQL:
This is the process of my analysis:
A = the result of a is true, and the counter is incremented by 1
The result of a < B is true, and the counter plus 1 results in 2
The result of B < C is true, and the counter plus 1 results in 3
When C < D, the result is trne counter plus 1, and the result is 4
And so on; The empid field is associated with itself. If the condition is less than or equal to, add 1 to the count data and write it into the following SQL,
In SQL statements, it is called a subquery.
SELECT
empid,
(
SELECT COUNT(*) FROM sales AS t2
WHERE t2.empid <= t1.empid
) AS rownum
FROM sales t1;

MySQL line number problem
The above statement function also has a performance problem. After testing, it is changed to the following:
SELECT empid,
(SELECT COUNT(*)
FROM sales AS t2
WHERE t2.qty < t1.qty OR (t2.qty = t1.qty AND t2.empid <= t1.empid)
) AS rownum
FROM
sales AS t1
ORDER BY qty,empid;
2.2 duplicate data analysis problems
First look at the following example data to create a digital auxiliary table nums
CREATE TABLE nums
(
a INT
);
INSERT INTO nums VALUES(1);
INSERT INTO nums VALUES(2);
INSERT INTO nums VALUES(3);
INSERT INTO nums VALUES(4);
INSERT INTO nums VALUES(5);
INSERT INTO nums VALUES(6);
INSERT INTO nums VALUES(7);
INSERT INTO nums VALUES(8);
INSERT INTO nums VALUES(9);
INSERT INTO nums VALUES(10);
select * from nums;
MySQL line number problem
Create duplicate data table t;
CREATE TABLE t (a CHAR(1));

INSERT INTO t SELECT ‘X’;
INSERT INTO t SELECT ‘X’;
INSERT INTO t SELECT ‘X’;
INSERT INTO t SELECT ‘Y’;
INSERT INTO t SELECT ‘Y’;
INSERT INTO t SELECT ‘Z’;

select * from t;
MySQL line number problem
Look at the data in table T. there are 3 records in X, 2 records in Y and 1 record in Z. how to solve the row number problem in this scenario? After analysis:

SELECT nums.a + smaller AS rownum,
C.a FROM
(
SELECT a,COUNT(*) AS cnt,
( SELECT COUNT(*) FROM t AS B
WHERE B.a < A.a) AS smaller
FROM t AS A
GROUP BY a
)AS C,nums
WHERE nums.a <= cnt;

MySQL line number problem

3. Summary questions:
In fact, the problem of row number data is often encountered in the development practice of MySQL. We do a two-step analysis. For data that are not multiple, we use sub query self connection to calculate the number. If it is duplicate data, we use data auxiliary table and sub query self connection to calculate it.