连接了两个表,我添加了代码来计算记录.问题是我想首先ORDER BY SN并在之后分配cnt？
我的代码是：
表
@H_301_11@ create table rot (
code int(10) primary key,PN varchar(10) not null,SN varchar(10) not null,LocID int(10) not null);
insert into rot values (1,'T1','T1SN1','1');
insert into rot values (2,'A1','A1SN1','2');
insert into rot values (3,'J1','J1SN1','3');
insert into rot values (4,'A2','A2SN1','1');
insert into rot values (5,'J2','J2SN1','2');
insert into rot values (6,'A3','A3SN1','3');
insert into rot values (7,'J3','J3SN1','4');
insert into rot values (8,'T1SN2','5');
insert into rot values (9,'A1SN2','1');
insert into rot values (10,'J2SN2','3');
insert into rot values (11,'J2SN3','4');
insert into rot values (12,'A1SN3','3');
insert into rot values (13,'J2SN4','5');
create table loc(
code1 int(10) primary key,LocVar varchar(10) not null);
insert into loc values (1,'AAA');
insert into loc values (2,'BBB');
insert into loc values (3,'CCC');
insert into loc values (4,'DDD');
insert into loc values (5,'EEE');
Cnt代码：
@H_301_11@SELECT * FROM rot
JOIN loc ON rot.code = loc.code1
JOIN (
SELECT t1.code,count(*) cnt FROM (
SELECT distinct code
FROM rot ts1
JOIN loc tx1 ON ts1.code = tx1.code1
) t1
JOIN (
SELECT distinct code
FROM rot ts2
JOIN loc tx2 ON ts2.code = tx2.code1
) t2 on t1.code <= t2.code
group by t1.code
) tt ON rot.code = tt.code
结果：
@H_301_11@+------+----+-------+-------+-------+--------+------+-----+
| code | PN | SN | LocID | code1 | LocVar | code | cnt |
+------+----+-------+-------+-------+--------+------+-----+
| 2 | A1 | A1SN1 | 2 | 2 | BBB | 2 | 4 |
| 4 | A2 | A2SN1 | 1 | 4 | DDD | 4 | 2 |
| 3 | J1 | J1SN1 | 3 | 3 | CCC | 3 | 3 |
| 5 | J2 | J2SN1 | 2 | 5 | EEE | 5 | 1 |
| 1 | T1 | T1SN1 | 1 | 1 | AAA | 1 | 5 |
+------+----+-------+-------+-------+--------+------+-----+
期望的结果
@H_301_11@+------+----+-------+-------+-------+--------+------+-----+
| code | PN | SN | LocID | code1 | LocVar | code | cnt |
+------+----+-------+-------+-------+--------+------+-----+
| 2 | A1 | A1SN1 | 2 | 2 | BBB | 2 | 1 |
| 4 | A2 | A2SN1 | 1 | 4 | DDD | 4 | 2 |
| 3 | J1 | J1SN1 | 3 | 3 | CCC | 3 | 3 |
| 5 | J2 | J2SN1 | 2 | 5 | EEE | 5 | 4 |
| 1 | T1 | T1SN1 | 1 | 1 | AAA | 1 | 5 |
+------+----+-------+-------+-------+--------+------+-----+
最佳答案
根据我的理解,并根据我对你的问题所作的评论,我将引用下一篇：
In the Results table and the logic of the query you have made,the
cnt column keep a counter of the number of codes that are greater or equal compared to the code value of the row. In other words,and
for example,code 2 is lower or equal than codes 2,3,4 and 5,so you
store a 4 on the cnt column. But,in the Desired Results this
has lost all sense,since you only save the position of the current
ordering on the cnt column.
假设您只需要在cnt列上使用SN的订单位置,您可以尝试下一个不依赖于MysqL 8.0并且不使用用户变量的解决方案：
@H_301_11@SELECT rot.*,loc.*,( SELECT COUNT(*)
FROM rot AS rot1
INNER JOIN loc AS loc1 ON loc1.code1 = rot1.code
WHERE rot1.SN <= rot.SN ) AS cnt
FROM
rot
INNER JOIN
loc ON loc.code1 = rot.code
ORDER BY
rot.SN