mysql批量更新数据

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

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

6 个解决方案

#1

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

#2

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

#3

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

#4

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;

#5

引用 1 楼 wandier 的回复:

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

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

#6

引用 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
一定要先分组在根据时间