如何创建两列中的值重复的数据子集?

Question

我有一个包含Names、Department、Rate、EmployeeID和其他几个字段的数据库，我想创建一个日期重复的员工子集。例如：

"Employees" "Department" "Rate" "EmployeeID" "Date"....
Bob          HR            19.5   09151       5/1/2019
Bob          HR            19.5   09151       5/2/2019
Bill         Accounting    20     09152       5/2/2019
Bob          HR            19.5   09151       5/2/2019
John         Accounting    21     09153       5/3/2019
Bill         Accounting    20     09152       5/2/2019
Jake         HR            23     09154       5/5/2019

应该返回

"Employees" "Department" "Rate" "EmployeeID" "Date"....
Bob          HR            19.5   09151       5/2/2019
Bill         Accounting    20     09152       5/2/2019
Bob          HR            19.5   09151       5/2/2019
Bill         Accounting    20     09152       5/2/2019

谢谢！

Answer 1

在 SQL 中，如果您有重复的行，则可以使用不同的子句

select distinct bNames, Department, Rate, EmployeeID
from my_table 

Answer 2

这是一个整洁的解决方案。

df_doubled <- df %>% 
group_by("Employees", "Department", "Rate", "EmployeeID", "Date") %>% 
count() %>% 
filter(n > 1) %>% 
uncount(n)

Answer 3

select * from table 
where Date in
(select Date from 
(select Date, count(*) c
from table
group by Date
having count(*) > 1))

Answer 4

您可以使用子查询来实现此目的：

select Employees, Department, Rate, EmployeeID, Date
from yourtable yt1
where 1 < (select count(*) from yourtable yt2 where yt1.Date = yt2.Date);