带有join、HAVING和GROUP BY的MySQL DELETE语句
带有join、HAVING和GROUP BY的MySQL DELETE语句
提问于 2012-12-27 16:24:28
我有一个项目表和另一个报表。每个报表都有一个外键，链接到要报告的项。
我正在尝试删除此查询中显示的所有项目：
SELECT items.id, title, SUM(weight) AS total_weight, SUM(weight)*10/views AS score
FROM items, reports
WHERE items.id = reports.item_id
GROUP BY items.id
HAVING score >= 50;尝试像这样的东西：
DELETE items
FROM (SELECT items.id, title, SUM(weight) AS total_weight, SUM(weight)*10/views AS score
FROM items, reports
WHERE items.id = reports.item_id
GROUP BY items.id
HAVING score >= 50)
AS T;显示以下错误消息：
ERROR 1109 (42S02): Unknown table 'items' in MULTI DELETE回答 4
Stack Overflow用户
回答已采纳
发布于 2012-12-27 16:37:07
在MySQL中，您必须小心使用子查询。我认为下面的方法是可行的：
DELETE FROM items
WHERE id IN (select *
from (SELECT items.id
FROM items join reports
on items.id = reports.item_id
GROUP BY items.id
HAVING SUM(weight)*10/views >= 50
)
)它通过使用附加子查询诱使编译器接受查询。我还修正了你的join语法。
但是，下面使用相关子查询将查询重写为更常见的语法：
delete from items
where exists (select r.item_id
from reports r
where r.item_id = items.item_id
group by r.item_id
having SUM(r.weight)*10/items.views >= 50
)这是猜测weight和views来自前面的reports. Otherwise, you need to put the项的别名。
Stack Overflow用户
发布于 2012-12-27 16:26:32
DELETE FROM items
WHERE
id IN (
SELECT
items.id
FROM items, reports
WHERE items.id = reports.item_id
GROUP BY items.id
HAVING SUM(weight)*10/views >= 50)Stack Overflow用户
发布于 2012-12-27 16:27:51
我相信您的delete语句是错误的。应该是delete from tablename where [condition]。
DELETE FROM items
WHERE
id IN (
Select T.id from (SELECT items.id, title, SUM(weight) AS total_weight, SUM(weight)*10/views AS score
FROM items, reports
WHERE items.id = reports.item_id
GROUP BY items.id
HAVING score >= 50) T)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14057791
复制相关文章
相似问题
从另一个QML设置QML属性
如何从函数内部更新QML属性
不更新属性别名的QML Javascript字典
QML从另一个QML对象访问附加属性
从C++更新QML窗口属性标志
添加站长 进交流群
领取专属 10元无门槛券
AI混元助手 在线答疑
关注 腾讯云开发者公众号
洞察 腾讯核心技术
剖析业界实践案例
腾讯云开发者
