根据时间对每天的数据进行排序
排序部分sql:
SELECT
id,
IF ( @pre_creattime = DATE_FORMAT( a.creattime, '%Y-%m-%d' ), @xh := @xh + 1, @xh := 1 ) xh,
@pre_creattime := DATE_FORMAT( a.creattime, '%Y-%m-%d' ) /*这里时需要分组排序的列,我这里是根据创建时间排序的*/
FROM ywgl_scrb_zyfs AS a,( SELECT @xh := 0, @pre_creattime := NULL ) r
ORDER BY
jstime ,
zdmc ;
全部SQL:
SELECT
t.id,
DATE_FORMAT( t.scrq, '%Y-%m-%d' ) scrq,
t1.zdmc,
t.zdbm,
t.zdlb,
t.cph,
t.sl,
t.cll,
t.jydw,
t.xczp,
t.ysdw,
t.cydd,
t.zbry,
t.fylb,
t.jsr,
DATE_FORMAT( t.jstime, '%Y-%m-%d' ) jstime,
t.ldh,
t.bz,
t.creattime,
t.cydd,
t.zbry,
round( b.xh, 0 ) xh
FROM
ywgl_scrb_zyfs t
LEFT JOIN ywgl_zdgl t1 ON t.zdmc = t1.id
LEFT JOIN ywgl_scrb_szhy_hzs y ON t.id = y.zbid
LEFT JOIN ywgl_scrb_zyfs_ss a ON a.id = t.id
INNER JOIN (
SELECT
id,
IF
( @pre_creattime = DATE_FORMAT( a.creattime, '%Y-%m-%d' ), @xh := @xh + 1, @xh := 1 ) xh,
@pre_creattime := DATE_FORMAT( a.creattime, '%Y-%m-%d' )
FROM
ywgl_scrb_zyfs AS a,(
SELECT
@xh := 0,
@pre_creattime := NULL
) r
ORDER BY
jstime ,
zdmc
) AS b ON t.id = b.id
ORDER BY
t.jstime desc,
b.xh desc,
zdmc DESC;