获取 SQL 表中两列相等的行组

发布于 2024-12-19 12:50:54 字数 553 浏览 2 评论 0原文

所以我搞砸了，用重复的行填充了一个表...我有一个像这样的表（实际上有数千个这样的东西:-(）：

id    gname     pname    value 
1     15         13       test 
2     15         13       test 
3     15         13       test
4     18         18       test
5     18         18       test 
6     18         18       test

这个表应该只允许一行相同的 gname、pname 和 value所以

现在我需要编写一些 sql 将所有这些公共行组合在一起并删除除一个之外的所有行！啊！

任何人都可以帮助我，第一步，我相信只是获得一条返回所有重复项组的语句。

**顺便说一句 - 我知道这是完全被劫持的。这是我继承的一个非常旧的系统，并且一个大客户正在使用它......所以我知道这些问题一旦我得到了。 db 恢复到原来的样子，

谢谢！

原文

So I screwed up and filled a table with duplicate rows... I have a table something like this (actually THOUSANDS of somethings like this :-( ) :

id    gname     pname    value 
1     15         13       test 
2     15         13       test 
3     15         13       test
4     18         18       test
5     18         18       test 
6     18         18       test

This table is only supposed to allow one row of the same gname, pname and value.

So now I need to write some sql that will group all these common rows together and delete them all except ONE! Argh!

Can anyone help me, STEP ONE, I believe is just to get a statement to return all the groups of duplicates.

** BTW - I know this is totally jacked. This is a very old system that I inherited and a big client this is using this... so I'm aware of the issues. I can fix the code that caused this ONCE I get the db back to the way its supposed to be.

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

傲影 2024-12-26 12:50:55

如果 id 是唯一的并且自动递增，这很容易：

DELETE Table
WHERE ID NOT IN (SELECT MIN(id)
                  FROM Table
                  GROUP BY gname, pname, value)

不过，首先执行 SELECT 。

If id is unique and auto-incrementing this is easy:

DELETE Table
WHERE ID NOT IN (SELECT MIN(id)
                  FROM Table
                  GROUP BY gname, pname, value)

Do a SELECT first, though.

回复收藏 0 原文

娜些时光，永不杰束 2024-12-26 12:50:55

试一试。它使用 RANK 函数来提取每个 gname/pname/value 元组中除第一个之外的所有元组。

DELETE a
FROM _table_ a
JOIN 
(
    SELECT id
        , RANK() OVER (PARTITION BY gname, pname, value ORDER BY id ASC) AS r
    FROM _table_
) b
    ON a.id = b.id
    AND b.r > 1

Give this a shot. It uses the RANK function to pull all but the first of each gname/pname/value tuple.

DELETE a
FROM _table_ a
JOIN 
(
    SELECT id
        , RANK() OVER (PARTITION BY gname, pname, value ORDER BY id ASC) AS r
    FROM _table_
) b
    ON a.id = b.id
    AND b.r > 1

回复收藏 0 原文

~没有更多了~