使用 order by 和 limit 从多个表中删除 [英] Delete from multiple tables using order by and limit
本文介绍了使用 order by 和 limit 从多个表中删除的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用 ORDER BY DESC 和 LIMIT 从具有关系的两个表中删除.
I'm trying to delete from two tables which have a relationship using ORDER BY DESC and LIMIT.
DELETE FROM my_rel_table AS t1
LEFT JOIN my_photo_table AS t2 ON t2.typeid = t1.typeid
WHERE t1.relid = 1
AND t1.type = 1
ORDER BY t1.id DESC
LIMIT 1
显然上述方法不起作用,因为 mysql 不接受使用内部联接的 order by 和限制.
Obviously the above does not work since mysql does not accept the order by and limit using an inner join.
表结构如下:
my_rel_table
id relid relno typeid type
int int int int tinyint
my_photo_table
typeid pos_x pos_y width height
int int int int int
推荐答案
对子选择使用 JOIN 来从 my_rel_table 中获取最高 id
Doing it using a JOIN against a subselect to get the highest id from the my_rel_table
DELETE my_rel_table, my_photo_table
FROM my_rel_table
INNER JOIN
(
SELECT MAX(id) AS MaxId
FROM my_rel_table
WHERE relid = 1
AND type = 1
) Sub1
ON my_rel_table.id = Sub1.MaxId
LEFT OUTER JOIN my_photo_table ON my_photo_table.typeid = my_rel_table.typeid
WHERE my_rel_table.relid = 1
AND my_rel_table.type = 1
没有直接测试,因为我没有测试数据!
Not directly tested as I have no test data!
编辑 - 几次尝试前 5 名,但再次未测试
EDIT - Couple of attempts to do the top 5, but again not tested
DELETE my_rel_table, my_photo_table
FROM my_rel_table
INNER JOIN
(
SELECT id
FROM my_rel_table
WHERE relid = 1
AND type = 1
ORDER BY id DESC
LIMIT 5
) Sub1
ON my_rel_table.id = Sub1.id
LEFT OUTER JOIN my_photo_table ON my_photo_table.typeid = my_rel_table.typeid
WHERE my_rel_table.relid = 1
AND my_rel_table.type = 1
或者换一种方式.
DELETE my_rel_table, my_photo_table
FROM my_rel_table
INNER JOIN
(
SELECT id, @Counter:=@Counter+1 AS ItemCounter
FROM my_rel_table
CROSS JOIN (SELECT @Counter:=0) Sub1
WHERE relid = 1
AND type = 1
ORDER BY id DESC
) Sub1
ON my_rel_table.id = Sub1.id
AND Sub1.ItemCounter <= 5
LEFT OUTER JOIN my_photo_table ON my_photo_table.typeid = my_rel_table.typeid
WHERE my_rel_table.relid = 1
AND my_rel_table.type = 1
这篇关于使用 order by 和 limit 从多个表中删除的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文