mysql 删除查询内部连接，

wrrgggsh 于 5个月前发布在 Mysql

关注(0)|答案(3)|浏览(57)

我做了一个删除查询，删除tbljournaalposten中volgr字段出现多次的所有记录。

delete from tbljournaalposten
where tbljournaalposten.ID in(
   SELECT
      tbljournaalposten.ID
   FROM
      invoerdatum 
   INNER JOIN
      rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
   INNER JOIN
      tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
   WHERE
      rabobank2_2.invoerdatum = invoerdatum.invoerdatum
   GROUP BY  tbljournaalposten.volgnr
   HAVING  tbljournaalposten.volgnr >1

   ORDER BY
      rabobank2_2.Datum DESC )

字符串
当我在phpmyadmin中尝试代码时，似乎什么都没有发生。它加载了很长时间。但是5分钟后查询停止。当我查看表时，volgnr > 1的recors被删除。当我只尝试此代码时，它工作良好，直接。

SELECT
  tbljournaalposten.ID
FROM
  invoerdatum 
INNER JOIN
  rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
INNER JOIN
  tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
WHERE
  rabobank2_2.invoerdatum = invoerdatum.invoerdatum
GROUP BY   tbljournaalposten.volgnr
HAVING  tbljournaalposten.volgnr >1

ORDER BY
  rabobank2_2.Datum DESC

型
我试了几次。我希望volgr >1的记录被删除。

mysql

来源：https://stackoverflow.com/questions/77674581/delete-query-inner-join-having

3条答案

按热度按时间

fkvaft9z1#

这个查询可能会删除那些行，这是你不想要的，所以只要确保不要在tbljournaalposten中丢失数据，（创建表的副本或类似的东西）。
我想你需要这样的东西：

delete from tbljournaalposten
where tbljournaalposten.volgnr in(
    SELECT volgnr FROM (
        SELECT
          tbljournaalposten.volgnr
        FROM
          invoerdatum 
        INNER JOIN
          rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
        INNER JOIN
          tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
        WHERE
          rabobank2_2.invoerdatum = invoerdatum.invoerdatum
        GROUP BY  tbljournaalposten.volgnr
        HAVING  count(DISTINCT tbljournaalposten.ID) > 1
    ) sub
);

字符串
子查询中的ORDER BY在这种情况下也没有意义。

赞(0）回复(0）举报 4个月前

2eafrhcq2#

我尝试了这个代码，但现在所有Volgnr> 1的记录都被删除了。

delete from tbljournaalposten
where tbljournaalposten.volgnr in(
SELECT * FROM (
    SELECT
      tbljournaalposten.volgnr
    FROM
      invoerdatum 
    INNER JOIN
      rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
    INNER JOIN
      tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
    WHERE
      rabobank2_2.invoerdatum = invoerdatum.invoerdatum
    GROUP BY  tbljournaalposten.volgnr
    HAVING  count(DISTINCT tbljournaalposten.ID) > 1
) sub
 );

字符串

赞(0）回复(0）举报 4个月前

6pp0gazn3#

我改了密码，现在可以用了。谢谢你的帮助。

delete from tbljournaalposten
where tbljournaalposten.ID in(
SELECT * FROM (
SELECT
  tbljournaalposten.ID
FROM
  invoerdatum 
INNER JOIN
  rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
INNER JOIN
  tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
WHERE
  rabobank2_2.invoerdatum = invoerdatum.invoerdatum
GROUP BY  tbljournaalposten.volgnr
HAVING  count(DISTINCT tbljournaalposten.ID) > 1
) sub
);

字符串

赞(0）回复(0）举报 4个月前

我来回答

mysql 删除查询内部连接，

3条答案

相关问题

热门标签

最新问答