根據查詢出的資料分組按照時間降序更新資料
如圖：
現在想把小明sort=1,小紅sort=2,小強sort=1
請問要怎么寫sql才能實作
uj5u.com熱心網友回復：
更新成1 2的邏輯關系是什么，固定的名稱對應？uj5u.com熱心網友回復：
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做欄位名是比較有問題的作法
uj5u.com熱心網友回復：
order by date , 相同日期使用相關的 sort ?uj5u.com熱心網友回復：
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;
uj5u.com熱心網友回復：
根據group_id分組之后在根據時間升序查詢出來的結果設定sort
uj5u.com熱心網友回復：
還要根據group_id分組 小明和小紅的group_id是一樣的，根據時間升序設定sort,小強的group_id和其他人的group_id不一樣所以他的sort=1
一定要先分組在根據時間
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/101024.html
標籤：MySQL
上一篇：【小白求解答】Mac終端運行mongod出現dyld: Library not loaded，鼓搗了一下午都不行？哭