MySQL：更新“排序”对应于ORDER BY的列 [英] MySQL: Update a "sort" column accoreding to ORDER BY

问题描述

完全编辑：
我的查询有2个表中的2列：文件名和排序顺序。

Fully edited: My query has 2 columns from 2 tables: filename and sortorder.

完整表在这里完成 http：// sqlfiddle .com /＃！9 / 8c0507

示例：

SELECT * FROM table1 t1
  LEFT JOIN table2 t2
    ON t1.idmd5 = t2.imgid
ORDER BY t2.filename
WHERE t1.anotherid = "123456";

输出：

filename-01.jpg   - 12
filename-02.jpg   - 73
filename-03.jpg   - 1
filename-12.jpg   - 63
filename-24.jpg   - 99

现在， sortorder列应根据ORDER BY-输出，应该从1开始。

Now, the "sortorder" column should be re-set according to the ORDER BY - output, it should start at 1.

预期结果：

filename-01.jpg   - 1
filename-02.jpg   - 2
filename-03.jpg   - 3
filename-12.jpg   - 4
filename-24.jpg   - 5

如何实现？

推荐答案

查询如下：

• 在排序后为每个文件名分配一个行号升序
订单。


• 以后在 INNER JOIN 之间进行表别名 t 和
  表 YT 匹配文件名并设置 t.rn（行号/新的
排序顺序）到 YT.sortOrder 列。

• Assigning a row number to each file name after sorting in ASCENDING ORDER.
• Later making an INNER JOIN between the table alias t and your table YT on matching filename and setting the t.rn (row number/new sort order) to the YT.sortOrder column.

UPDATE 
your_table YT 
INNER JOIN 
(
    SELECT 
        fileName,
        @rowNumber := @rowNumber + 1 AS rn
    FROM your_table , (SELECT @rowNumber := 0) var
    ORDER BY fileName ASC
) AS t
ON YT.fileName = t.fileName
SET YT.sortOrder = t.rn;

演示 更新前

访问SQL FIDDLE的问题

Facing problems to access SQL FIDDLE

测试模式（带有数据）：

DROP TABLE IF EXISTS `your_table`;
CREATE TABLE `your_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(50) NOT NULL,
  `sortOrder` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `your_table` VALUES ('1', 'f1', '12');
INSERT INTO `your_table` VALUES ('2', 'f2', '73');
INSERT INTO `your_table` VALUES ('3', 'f3', '1');
INSERT INTO `your_table` VALUES ('4', 'f4', '63');
INSERT INTO `your_table` VALUES ('5', 'f5', '99');

SELECT 
*
FROM your_table;

id  filename   sortOrder
1      f1         12
2      f2         73
3      f3          1
4      f4         63
5      f5         99

**Now Run the above query**



SELECT 
*
FROM your_table;

最终输出：

id  filename   sortOrder
1      f1         1
2      f2         2
3      f3         3
4      f4         4
5      f5         5

编辑：（根据要求的变化）

UPDATE 
tableone TOne 
INNER JOIN 
(
  SELECT
        t1.file_id,
        t2.IMG_FILENAME,
        @rowNumber := @rowNumber + 1 AS rn
    FROM
    tableone t1
    LEFT JOIN tabletwo t2 ON t1.FILE_ID = t2.IMG_ID
    CROSS JOIN (SELECT @rowNumber := 0) AS var
    ORDER BY t2.IMG_FILENAME ASC
) AS t
ON TOne.file_id = t.file_id
SET TOne.Order = t.rn;

测试数据和架构：

DROP TABLE IF EXISTS `tableone`;
CREATE TABLE `tableone` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order` int(11) NOT NULL,
  `file_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `tableone` VALUES ('1', '12', '1');
INSERT INTO `tableone` VALUES ('2', '73', '2');
INSERT INTO `tableone` VALUES ('3', '1', '3');
INSERT INTO `tableone` VALUES ('4', '63', '4');
INSERT INTO `tableone` VALUES ('5', '99', '5');


DROP TABLE IF EXISTS `tabletwo`;
CREATE TABLE `tabletwo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `img_id` int(11) NOT NULL,
  `img_filename` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `tabletwo` VALUES ('1', '1', 'filename-01.jpg ');
INSERT INTO `tabletwo` VALUES ('2', '2', 'filename-02.jpg ');
INSERT INTO `tabletwo` VALUES ('3', '3', 'filename-03.jpg ');
INSERT INTO `tabletwo` VALUES ('4', '4', 'filename-04.jpg ');
INSERT INTO `tabletwo` VALUES ('5', '5', 'filename-05.jpg ');

运行以上命令（ UPDATE ）查询操作并查看结果

Run the above (UPDATE) query operation and see the result

更新前： SQL本地演示

更新后： SQL FIDE DEMO

这篇关于MySQL：更新“排序”对应于ORDER BY的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！