更新表中的订单值 - mysql 选择查询有多个连接 [英] Update order value in table - mysql select query has multiple joins

问题描述

我对 mySQL 相当擅长，但出于某种原因，我正在努力解决这个问题.

I'm pretty good with mySQL but for some reason I'm struggling to get my head around this one.

我需要以编程方式更新 wp_esp_ticket 表中的订单值，因为有时日期会乱序.这是我必须用来带回所有相互关联且需要排序的日期的查询:

I need to programmatically update the order values in the wp_esp_ticket table as the dates get out of order sometimes. This is the query I have to use to bring back all the dates that are related to each other and need ordering:

SELECT * FROM `wp_esp_ticket` et 
INNER JOIN wp_esp_datetime_ticket edtt on edtt.TKT_ID = et.TKT_ID
INNER JOIN wp_esp_datetime edt on edtt.DTT_ID = edt.DTT_ID
WHERE edt.EVT_ID = 1325

这会以正确的顺序带回所有需要更新的行，但是 wp_esp_ticket 表中的 TKT_order 列然后乱序了.我需要在 TKT_order 列中将顶行的值设为 1，并在下面的每一行中以 1 为增量递增.

This brings back all the rows that need updating in the correct order, but the TKT_order column in the wp_esp_ticket table is then out of sequence. I need to give the top row a value of 1 in the column TKT_order and go up in increments of 1 for each row below.

我需要为此编写一个整洁的 sql 语句，因为它需要与另一段向数据库添加新日期的代码一起运行.

I need to put together a tidy sql statement for this as it will need to be run along with another peice of code that adds new dates to the database.

谢谢！！！

更新:

多亏了 Drew 的投入和指导，我才开始工作:

Thanks to the input and direction from Drew I got this to work:

SET @newnum = 0;

Update wp_esp_ticket tix

INNER JOIN (
SELECT TKT_ID FROM `wp_esp_ticket` et 
INNER JOIN wp_esp_datetime_ticket edtt on edtt.TKT_ID = et.TKT_ID
INNER JOIN wp_esp_datetime edt on edtt.DTT_ID = edt.DTT_ID
WHERE edt.EVT_ID = 1325
ORDER BY edt.DTT_EVT_start ASC
) b ON tix.TKT_ID = b.TKT_ID

SET tix.TKT_order = @newnum:=@newnum + 1

推荐答案

来自 手册 靠近页面顶部.

From the Manual near the top of the page.

例如，如果表的 id 列中包含 1 和 2，而 1 是在2更新为3之前更新为2，发生错误.为避免这种情况问题，添加一个ORDER BY子句，导致id较大的行要在具有较小值的值之前更新的值:

For example, if the table contains 1 and 2 in the id column and 1 is updated to 2 before 2 is updated to 3, an error occurs. To avoid this problem, add an ORDER BY clause to cause the rows with larger id values to be updated before those with smaller values:

UPDATE t SET id = id + 1 ORDER BY id DESC;

UPDATE t SET id = id + 1 ORDER BY id DESC;

请注意，这是手册中的概念.相应地进行调整.

Note that is a conceptual from the manual. Tweak accordingly.

将更新/排序与update with a join的概念结合起来(How在 UPDATE 查询中做 3 个表 JOIN?)，你就准备好了.

Combine the update / order by with the concept of update with a join (How to do 3 table JOIN in UPDATE query?) and you are all set.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

在你的情况下，我不同意，使用 rownum:

In your case, I diverge, going with a rownum:

set @rownum:=0;

update `wp_esp_ticket` et 
INNER JOIN wp_esp_datetime_ticket edtt on edtt.TKT_ID = et.TKT_ID
INNER JOIN wp_esp_datetime edt on edtt.DTT_ID = edt.DTT_ID
set TKT_order =@rownum:=@rownum+1
WHERE edt.EVT_ID = 1325

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

see this rough example in action:

create table j
(   k varchar(20) not null,
    theOrder int not null
);

insert j(k,theOrder) values ('z',-1),('gj',-1),('w',-1),('h',-1),('uw',-1),('b',-1);

set @rownum:=0;
update j
set theOrder=@rownum:=@rownum+1
order by k;

+----+----------+
| k  | theOrder |
+----+----------+
| b  |        1 |
| gj |        2 |
| h  |        3 |
| uw |        4 |
| w  |        5 |
| z  |        6 |
+----+----------+

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

这篇关于更新表中的订单值 - mysql 选择查询有多个连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！