mysql order by 计算_mysql – 在ORDER BY之后计算记录

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

连接了两个表,我添加了代码来计算记录.问题是我想首先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开头.