mysql> select * from a; +------+------+---------------------+------+ | id | name | d | sort | +------+------+---------------------+------+ | 1 | 小明 | 2017-12-20 07:33:02 | NULL | | 3 | 小强 | 2017-12-20 07:33:19 | NULL | | 2 | 小红 | 2017-12-20 07:33:30 | NULL | +------+------+---------------------+------+ update a a1 set sort=(select count(*) from (select * from a) a2 where a2.d<a1.d)+1; select * from a; +------+------+---------------------+------+ | id | name | d | sort | +------+------+---------------------+------+ | 1 | 小明 | 2017-12-20 07:33:02 | 1 | | 3 | 小强 | 2017-12-20 07:33:19 | 2 | | 2 | 小红 | 2017-12-20 07:33:30 | 3 | +------+------+---------------------+------+注意mysql的子查询有点怪异,直接用 update a a1 set sort=(select count(*) from a a2 where a2.d<a1.d) 会报错:ERROR 1093 (HY000): You can't specify target table 'a1' for update in FROM clause 所以要多套一层 另外用date做字段名是比较有问题的作法
order by date , 相同日期使用相关的 sort ?
CREATE TABLE table1(id int, name varchar(10), date datetime, sort int) AS SELECT 1 as id, 'aa' as name, '2017-12-1 17:00' as date UNION ALL SELECT 2 as id, 'BB' as name, '2017-12-2 18:00' as date UNION ALL SELECT 3 as id, 'CC' as name, '2017-12-1 13:00' as date ; UPDATE table1 A, ( SELECT id, sort FROM( SELECT @id := CASE WHEN TIMESTAMPDIFF(day, @dt, date) = 0 THEN @id ELSE @id+1 END as sort, @dt := date, id FROM table1, (SELECT @dt:=NULL, @id:=0) x ORDER BY date )B ) B SET A.sort = B.sort WHERE A.id = B.id ; SELECT * FROM table1;
+------+------+---------------------+------+
| id | name | d | sort |
+------+------+---------------------+------+
| 1 | 小明 | 2017-12-20 07:33:02 | NULL |
| 3 | 小强 | 2017-12-20 07:33:19 | NULL |
| 2 | 小红 | 2017-12-20 07:33:30 | NULL |
+------+------+---------------------+------+
update a a1 set sort=(select count(*) from (select * from a) a2 where a2.d<a1.d)+1;
select * from a;
+------+------+---------------------+------+
| id | name | d | sort |
+------+------+---------------------+------+
| 1 | 小明 | 2017-12-20 07:33:02 | 1 |
| 3 | 小强 | 2017-12-20 07:33:19 | 2 |
| 2 | 小红 | 2017-12-20 07:33:30 | 3 |
+------+------+---------------------+------+注意mysql的子查询有点怪异,直接用
update a a1 set sort=(select count(*) from a a2 where a2.d<a1.d)
会报错:ERROR 1093 (HY000): You can't specify target table 'a1' for update in FROM clause
所以要多套一层
另外用date做字段名是比较有问题的作法
AS
SELECT 1 as id, 'aa' as name, '2017-12-1 17:00' as date UNION ALL
SELECT 2 as id, 'BB' as name, '2017-12-2 18:00' as date UNION ALL
SELECT 3 as id, 'CC' as name, '2017-12-1 13:00' as date
;
UPDATE table1 A, (
SELECT id, sort FROM(
SELECT
@id := CASE WHEN TIMESTAMPDIFF(day, @dt, date) = 0 THEN @id ELSE @id+1 END as sort,
@dt := date,
id
FROM table1, (SELECT @dt:=NULL, @id:=0) x
ORDER BY date
)B
) B
SET A.sort = B.sort
WHERE A.id = B.id
;
SELECT * FROM table1;
根据group_id分组之后在根据时间升序查询出来的结果设置sort
一定要先分组在根据时间