我有代码来计算记录,但无法在它之前添加订单.

连接了两个表,我添加了代码来计算记录.问题是我想首先ORDER BY SN并在之后分配cnt？

我的代码是：

表

 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代码：

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

结果：

+------+----+-------+-------+-------+--------+------+-----+
| 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 |
+------+----+-------+-------+-------+--------+------+-----+

期望的结果

+------+----+-------+-------+-------+--------+------+-----+
| 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 |
+------+----+-------+-------+-------+--------+------+-----+

我只是想知道在哪里放置ORDER BY？在我的代码中,我无法分配变量,代码必须以SELECT开头.