如何避免团体，但需要最低数量？ [英] How to avoid groups but require a minimum count?

问题描述

我已经回答了很多关于获得最大n组的问题，但现在发现自己需要相反的结果。

我有一个结果集，显示学生，日期和项目，代表哪些学生在某一天的项目中工作。

我希望看到有多名学生在当天为一个项目工作的行。因此，如果我的结果集如下所示：

  |学生|日期|项目| 
 + --------- + ------------ + --------- + 
 | 1 | 2014-12-04 | 1 | 
 | 2 | 2014-12-04 | 1 | 
 | 3 | 2014-12-04 | 1 | 
 | 1 | 2014-12-03 | 1 | 
  

我只想看前三行，所以我可以看到学生1,2， 3同一天在同一个项目上一起工作。我可以这样过滤：

  GROUP BY日期，项目
 HAVING COUNT（*）> 1 
  

但是只有一行会被返回。

解决方案

您可以使用您现有的查询作为子查询并获得结果

SQL FIDDLE DEMO

<$ p $从表1中选择日期，项目

按日期分组，项目
（
）从表格1中选择*
JOIN
在t1.date = t.date
和t1.project = t.project
上有count（*）> 1
）t
。 / pre>

I have answered and read many question on getting the greatest-n-per-group but now find myself needing the opposite.

I have a result set that shows students, date, and project that represent which students worked on a project on a given day.

I would like to see rows where multiple students worked on a project for that day. So if my result set looks like this:

| student |    date    | project |
+---------+------------+---------+
|    1    | 2014-12-04 |    1    |
|    2    | 2014-12-04 |    1    |
|    3    | 2014-12-04 |    1    |
|    1    | 2014-12-03 |    1    |

I would only like to see the first three rows, so I can see that students 1,2,3 worked together on the same project on the same day. I could filter like this:

GROUP BY date, project
HAVING COUNT(*) > 1

But then only one row will be returned.

解决方案

you can use your existing query as subquery and get the results

SQL FIDDLE DEMO

SELECT * from Table1 T1
JOIN
(
  SELECT date, project
  from table1
  group by date, project
  having count(*) >1
) t
on t1.date = t.date
and t1.project = t.project

这篇关于如何避免团体，但需要最低数量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！