mysql 利用自增数据项的方法,对同一个表有某种关联的数据进行处理。(利用增加一项的方法)

问题背景:

stage表有app_id,stage_id,submission_time等数据项。要求对该表的指定app_id的所有stage_id ,按submission_time 从小到大排序,并且相邻的两项进行做差。

另一个描述如下:

按submission_time排序,第一个stage_id的submission_interval=0;

第二个stage_id的submission_interval=(第二个stage_id的submission_time-第一个stage_id的submission_time),也就是,第二个stage_id到第一个stage_id的时间间隔。。。依次类推。

select a.app_id, a.stage_id, a.submission_time, count(*)

子句:(利用增加一项的方法,增加了rownum字段)
select a.app_id, a.stage_id, a.submission_time, count(*)as rownum

from stagea,stageb

and
a.submission_time >b.submission_time

group by a.app_id, a.stage_id , a.submission_time

说明:按submission_time排序,submission_time项是不相同的。

表a 和 表b(同一个表) 进行笛卡尔积,选择

a的每一项submission_time 大于b的submission_time所有项分别有

0,1,2.。。。。。。。。。。。。

所以count(*)as rownum的序号可以作为submission_time的排序。

语法形式:

select * from A right join B on 条件 group by 项。

select B.submission_time-A.submission_time from

(select a.app_id, a.stage_id, a.submission_time, count(*)as rownum from stage a ,stage b where a.app_id="XXXXXXXXXX" and b.app_id=a.app_id and a.submission_time >b.submission_time group by a.app_id, a.stage_id , a.submission_time ) as A

right join

(select a.app_id, a.stage_id,a.submission_time, count(*) as rownum from stage a ,stage b where a.app_id="XXXXXXXXXX" and b.app_id=a.app_id and a.submission_time > b.submission_time group by a.app_id, a.stage_id , a.submission_time ) as B
on A.rownum+1=B.rownum

group by B.rownum ;