选择计数为某个值的多个记录

There is a huge database with more than 500k values, but with only one table having all the data. I need to extract some of it for a given condition.

有一个超过500k的大型数据库，但是只有一个表拥有所有的数据。我需要在给定的条件下提取一些。

Table structure is like this,

表格结构是这样的，

column_a | column_b    
A        | 30    
A        | 40    
A        | 70    
B        | 25    
B        | 45    
C        | 10    
C        | 15    
C        | 25

I need to extract all the data having a count(column_a) = 3. the catch is that i need to get all the three records too. Like this,

我需要提取具有count(column_a) = 3的所有数据。问题是，我还需要把这三张唱片都买下来。像这样,

column_a | column_b 
A        | 30    
A        | 40    
A        | 70    
C        | 10    
C        | 15    
C        | 25

I have tried to do this with a query like this

我试过用这样的查询来做这个

select column_a,column_b group by column_a having count(*)=3;

Here i get the correct values for column_a but only one record from each.

这里我得到了column_a的正确值，但是每个记录只包含一条。

Thanks in advance, Bhashithe

谢谢你提前,Bhashithe

2 个解决方案

#1

One approach is to INNER JOIN your original table to a subquery which identifies the column_a records which come in groups of exactly 3.

一种方法是将原始表内部连接到一个子查询，该子查询标识column_a记录，这些记录以3为一组。

SELECT t1.column_a, t1.column_b
FROM table t1
INNER JOIN
(
    SELECT column_a, COUNT(*)
    FROM table
    GROUP BY column_a
    HAVING COUNT(*) = 3
) t2
ON t1.column_a = t2.column_a

#2

You can use nested query, if you want. Here, inner query fetches the records having column_a size equals to 3 and outer query displays all the records using the 'IN' clause.

如果需要，可以使用嵌套查询。在这里，内部查询获取具有column_a大小为3的记录，外部查询使用“IN”子句显示所有记录。

SELECT t.column_a, t.column_b FROM table t
WHERE t.column_a IN 
(
    SELECT t1.column_a FROM table t1
    GROUP BY t1.column_a
    HAVING COUNT(t1.column_a) = 3
) 
ORDER BY t.column_a;

#1