MySQL查询结果条数编号排序

月黑风高孙 发布于 2017/02/28 14:06
阅读 331
收藏 0
SELECT @row := @row + 1 AS number,date_add('1900-01-01',interval floor(datediff(inspection.time,'1900-01-01')/7)*7 day) as week_start,date_add('1900-01-01',interval floor(datediff(inspection.time,'1900-01-01')/7)*7+6 day)as week_end,count(inspection.id) as total,count(case when inspection.state=0 then inspection.id end ) as normal_totals,count(case when inspection.state=1 then inspection.id end ) as abnormal_totals FROM (SELECT @row := 0) r,ez_inspection inspection LEFT JOIN ez_rivers rivers ON inspection.rivers_id = rivers.id WHERE inspection.rivers_type_id = '1' GROUP BY floor(datediff(inspection.time,'1900-01-01')/7) ORDER BY week_start asc LIMIT 0,20

 

第二种:按wee_start降序

SELECT @row := @row + 1 AS number,date_add('1900-01-01',interval floor(datediff(inspection.time,'1900-01-01')/7)*7 day) as week_start,date_add('1900-01-01',interval floor(datediff(inspection.time,'1900-01-01')/7)*7+6 day)as week_end,count(inspection.id) as total,count(case when inspection.state=0 then inspection.id end ) as normal_totals,count(case when inspection.state=1 then inspection.id end ) as abnormal_totals FROM (SELECT @row := 0) r,ez_inspection inspection LEFT JOIN ez_rivers rivers ON inspection.rivers_id = rivers.id WHERE inspection.rivers_type_id = '1' GROUP BY floor(datediff(inspection.time,'1900-01-01')/7) ORDER BY week_start desc LIMIT 0,20

number为2的那条一直跟着 week_start为02-13走 其实我是想 week_start降序排列 获得如下结果:

 

 

加载中
0
tostarrysky
tostarrysky

  select (@row := @row + 1) number,t.*    from T  t ,(SELECT @row := 0) r  order by t.time desc; 这样应该可以 。。

返回顶部
顶部