根据另一个表中的日期查找重复行

I have two Tables. T1 and T2.

我有两张桌子。 T1和T2。

Table T1:

dno     jno
1       a
1       b
1       c

Table T2:

jno     date
a       2017
b       2017
c       2016

I want to get duplicate rows in T1 based on date in T2. If I do this:

我希望根据T2中的日期在T1中获取重复行。如果我这样做:

select T1.dno, count(T1.dno) count from T1 left join T2 on T1.jno = T2.jno
group by T1.dno
having date=2017

T1.dno gets grouped together and I can't find out the duplicates.

T1.dno被组合在一起,我找不到重复项。

Expected Output:

dno count
1   2

Any tips?

4 个解决方案

#1

EDIT: your question looks so vague and i may not have understood it.

编辑:你的问题看起来很模糊,我可能没有理解。

But anyway, based on your expected output your first query was ok, just change the HAVING clause and make it a WHERE clause

但无论如何,根据您的预期输出,您的第一个查询是正常的,只需更改HAVING子句并使其成为WHERE子句

select T1.dno, count(T1.dno) count
from T1
left join T2 on T1.jno = T2.jno
WHERE T2.DATE = 2017
group by T1.dno

#2

The below query should help you

以下查询应该对您有所帮助

select rno, count(date) cnt
from t2
where t2.rno in (select T1.rno from T1)
group by rno
having count(date) > 1 -- this will get you the duplicate records

#3

Your expected output can be gotten by the following query:

您可以通过以下查询获得预期的输出:

SELECT t1.dno, COUNT(DISTINCT t2.date) AS count
FROM T1 t1
LEFT JOIN T2 t2
    ON t1.jno = t2.jno
GROUP BY t1.dno;

But it is not clear what role T1 plays here.

但目前尚不清楚T1在这里扮演什么角色。

#4

You could use this. It'll give you the the year and how many records were returned for that year.

你可以用它。它将为您提供当年以及当年返回的记录数量。

select t1.dno,t2.date,count(t2.date) 
from t1 inner join t2 on t2.jno = t1.jno
group by t1.dno,t2.date
having count(t2.date) >1

#1