Mysql only showing rows with 2 or more values in a column

khbbv19g 于 2022-12-22 发布在 Mysql

关注(0)|答案(2)|浏览(159)

I have a query results that is similar to the following:
| Part ID | Company |
| ------------ | ------------ |
| 102 | ABC Corp |
| 102 | XYZ Corp |
| 103 | ABC Corp |
| 104 | EFG Corp |
| 104 | HIJ Corp |
This table is created with a simple query such as SELECT part_id, company FROM MyTable
However, I only want to return rows where the part ID number occurs more than once, i.e. I want the '103' part ID row taken out, as it has only one listing.
I tried using COUNT(*) but this returns '1' as the rows are always going to be unique. I want to remove rows where the COUNT of the total occurrences of a Part ID is only 1.

mysql

来源：https://stackoverflow.com/questions/74858506/mysql-only-showing-rows-with-2-or-more-values-in-a-column

2条答案

按热度按时间

9jyewag01#

You can use

SELECT part_id, company
FROM   mytable
WHERE  part_id IN (SELECT part_id
                   FROM   mytable
                   GROUP  BY part_id
                   HAVING Count(*) > 1)

Output will be

Part ID Company
102 ABC Corp
102 XYZ Corp
104 EFG Corp
104 HIJ Corp

赞(0）回复(0）举报 2022-12-22

ivqmmu1c2#

试试这个：

select * from
(SELECT part_id, company, count(pard_id) as count
FROM MyTable group by part_id,company) as a
where a.count>1;

赞(0）回复(0）举报 2022-12-22

我来回答

Mysql only showing rows with 2 or more values in a column

2条答案

相关问题

热门标签

最新问答