sql - SQL - 删除多个条目

Question

我一直在尝试自学一些基本的 SQL，以便在我使用数据库管理创建的应用程序中实现。

实施后很快，我意识到我不确定如何处理重复条目。在网上看，似乎我需要为每条可能重复的数据分配一个“唯一”约束，但我想知道我是否可以以不同的方式处理这个问题。

总之，我想出了一个不同的想法来解决这个问题，我不确定如何用 SQL 编写它，或者即使它是一个可行的想法。

我想要一些外部输入来帮助我完成这个或反驳我的想法。

使用 dart 语言，我可以查找所有重复的条目，以及存在多少重复项。这就是我用来获得此结果的方法：

SELECT `title`, COUNT(*) FROM `Article` GROUP BY `url` HAVING COUNT(*) > 1

这就是结果

flutter: [{title: US Stock Market Chalks up Huge Weekly Losses; Bitcoin, DXY Impress, COUNT(*): 2}, {title: What Triggered Ethereum to Rise 66% Within 1 Week? Factors and Trends, COUNT(*): 26}, {title: What’s Next for Crypto Miners?, COUNT(*): 2}]

我可以按 COUNT(*) 减一返回的数量删除所有重复的结果吗？

这意味着如果我有 26 个重复项What Triggered Ethereum to Rise 66% Within 1 Week? Factors and Trends，我可以删除 26 所代表的数字COUNT(*)，但在从数字 26 中删除减去 1 之前，实际上只剩下一个重复项？（是使用上述“唯一”约束的预期结果）

编辑：这是我用来向数据库输入新数据行的 SQL 行。

'INSERT INTO Article(title, description, url, urltoimage, publishedat) VALUES("$title", "$description", "$url", "$urlToImage", "$publishedAt")'

标签： sqlsqlitedart

Answer 1

您可以删除每个 url 的所有 bar 1 条目 - 假设 url 列应该是唯一的 - 如果不仅仅是 url 列或另一列的组合只需更改前两个 WHERE 子句。- 还假设该表不是 WITHOUT ROWID 表。

:-

SELECT count() FROM article WHERE url = 'myurl';
WITH cte AS (
    SELECT rowid AS thisrowid
    FROM article 
    WHERE url = 'myurl'
    ORDER by url
    LIMIT (SELECT count(*)-1  FROM article WHERE url = 'myurl')
)
DELETE FROM article WHERE rowid IN (SELECT thisrowid FROM cte);

这使用 CTE（通用表表达式（临时表））根据选择标准（WHERE 子句）从文章表的所有 bar 1 行中提取rowid（一个特殊的通常隐藏的列，除非指定 WITHOUT ROWID，否则始终存在） （在这种情况下，url 列包含 myurl）。即使用 LIMIT，这是根据相同 WHERE 子句减去 1 的行数。

这个 CTE，一个 rowids 列表然后用作 DELETE 的 WHERE 子句的基础。

工作示例 SQL

以上是使用以下 SQL 测试的：-

DROP TABLE IF EXISTS article;
CREATE TABLE IF NOT EXISTS article (title TEXT, description TEXT, url TEXT, urltoimage TEXT, publisheddat);
INSERT INTO article VALUES
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01')
;
SELECT count() FROM article WHERE url = 'myurl';
WITH cte AS (
    SELECT rowid
    FROM article 
    WHERE url = 'myurl'
    ORDER by url
    LIMIT (SELECT count(*)-1  FROM article WHERE url = 'myurl')
)
-- SELECT * FROM cte;
DELETE FROM article WHERE rowid IN (SELECT * FROM cte);
SELECT count() FROM article WHERE url = 'myurl';

两个查询的结果是：-

删除前的行数：-

剩余行数：-

---

替代更简单并修复问题再次发生

说这可能很容易

1. 创建一个新表，例如 article_new 表，包括 UNIQUE 约束但其他方面相同，并且
2. 然后从现有表中填充它（使用 INSERT OR IGNORE 在删除重复项时不会失败）。
3. 然后重命名原始表（可以删除但更安全），例如为 article_old
4. 然后将新表重命名为原始表名
5. 最后删除重命名的（如果愿意的话）。

代码可能是（再次假设 url 列需要是唯一的）：-

DROP TABLE IF EXISTS article_new;
DROP TABLE IF EXISTS article_old;
CREATE TABLE IF NOT EXISTS article_new (title TEXT, description TEXT, url TEXT UNIQUE, urltoimage TEXT, publisheddat);
INSERT OR IGNORE INTO article_new SELECT * FROM article;
ALTER TABLE article RENAME TO article_old;
ALTER TABLE article_new RENAME TO article;
DROP TABLE IF EXISTS article_old;

工作示例（注意更多行添加了 3 个不同的 url 列）：-

DROP TABLE IF EXISTS article;
CREATE TABLE IF NOT EXISTS article (title TEXT, description TEXT, url TEXT, urltoimage TEXT, publisheddat);
INSERT INTO article VALUES
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl2','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl3','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl3','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl3','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl3','myimageurl','2018-01-01'),
    ('Title001','Description001','myurl3','myimageurl','2018-01-01')
;

DROP TABLE IF EXISTS article_new;
DROP TABLE IF EXISTS article_old;
CREATE TABLE IF NOT EXISTS article_new (title TEXT, description TEXT, url TEXT UNIQUE, urltoimage TEXT, publisheddat);
INSERT OR IGNORE INTO article_new SELECT * FROM article;
ALTER TABLE article RENAME TO article_old;
ALTER TABLE article_new RENAME TO article;
DROP TABLE IF EXISTS article_old;
SELECT * FROM article;

结果 ：-