Finding records with equal values in different fields?

问题: I have a people table, two of this table fields are id and country. I want to find all records that have the same id and country, because id should be unique per country....

问题:

I have a people table, two of this table fields are id and country.

I want to find all records that have the same id and country, because id should be unique per country.

What would be the right way do do that?

for example, sample output should be:

+--------------+---------+
| id           | country |
+--------------+---------+
| 123          | AT      |
| 123          | AT      |
| 456          | GB      |
| 456          | GB      |
| 456          | GB      |
| 789          | AU      |
| 789          | AU      |

回答1:

Simple GROUP BY with HAVING clause will suffice:

SELECT id, country
FROM people
WHERE id <> "" AND id IS NOT NULL
GROUP BY id, country
HAVING COUNT(*) > 1

回答2:

    SELECT p1.* FROM people p1
    INNER JOIN people p2
    ON p1.id = p2.id AND p1.country = p2.country
    ORDERY BY p1.id

回答3:

this may work

 with cte as (SELECT id -- extracts ID for countryes present more than 1 time
FROM people
GROUP BY id, country
HAVING COUNT(*) > 1)

select id, country -- adds repeated values
from people p join cte on p.id=cte.id
  • 发表于 2019-01-28 22:09
  • 阅读 ( 43 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除