检查表中同一对列值的sql查询将是什么? [英] what will be the sql query for checking same pairs of column values in a table?

问题描述

我有一个表emp(id，name，salary)且没有主键(我知道这是一个坏主意，但是我的工作在不一致的表上)
我希望查询检索具有相同对值(id，名称)的元组 我尝试使用自我加入，但没有得到确切的陈述. 在这里，同一对正在考虑，但我正在为两个以上的属性进行比较.请帮助 例子

I have a table emp(id , name, salary) and having no primary key (i know that is a bad idea but my work is on inconsistent table)
I want a query to retrieve tuples which have same pairs of values (id, name) i tried using self join but not getting exact statement. Here same pair is considering but i am working for more than 2 attributes for comparison. plz help Example

     id     name   salary
     1      a       100
     1      b       100
     2      a       200

     1      a       300
     2      c       200

所以我想找到具有相同ID和名称值的元组.这是(1，a)

So i want to find tuple with same value of id and name. here it is(1,a)

推荐答案

您可以通过分组并仅选择计数大于1的记录来找到它们.

You can find them by grouping and only choosing those record where the count is greater than 1:

SELECT ID, Name
FROM Table
GROUP BY ID, Name
HAVING COUNT(*) > 1

这篇关于检查表中同一对列值的sql查询将是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！