mysql批量更新数据

[k851590063]

根据查询出的数据分组按照时间降序更新数据

如图：
现在想把小明sort=1,小红sort=2,小强sort=1
请问要怎么写sql才能实现

[wandier]

更新成1 2的逻辑关系是什么，固定的名称对应？

[ckc]

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做字段名是比较有问题的作法

[zjcxc]

order by date , 相同日期使用相关的 sort ?

[zjcxc]

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;

[k851590063]

引用 1 楼 wandier 的回复:

更新成1 2的逻辑关系是什么，固定的名称对应？

根据group_id分组之后在根据时间升序查询出来的结果设置sort

[k851590063]

引用 2 楼 ckc 的回复:

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做字段名是比较有问题的作法

还要根据group_id分组 小明和小红的group_id是一样的，根据时间升序设置sort,小强的group_id和其他人的group_id不一样所以他的sort=1 一定要先分组在根据时间