Getting number of common terms in two tables where there are duplicates

Consider the following two tables in SQL Server 2008:

Table1 which has just one int field: Term1

Table2 which also just has one int field: Term2. (Yeah, I know - I lack imagination.)

.

Let's say that I insert the following values in Table1: 1, 2, 5, 5, 6, 7

And I insert the following values in Table2: 3, 4, 5, 5, 8, 9

.

I want to run a query where I find the terms that are common to both tables, and I want these paired off. So when there are duplicates (as there are in this case - 5 and 5), I want there to be a single row for each pair found. In other words, I would like two pairs of 5s returned as the two terms that are common.

How do I do this? If I do a simple inner join on Table1 and Table2, I get four pairs of 5's returned.

.

EDIT: I should have made this clearer, I'm interested in any common values, not just duplicates. The thing is that duplicates are harder to deal with.

So if I insert the following values in Table1: 1, 2, 5, 5, 5, 6, 7, 8

And I insert the following values in Table2: 3, 4, 5, 5, 5, 6, 9, 0

Then I want to see 5, 5, 5, 6 as the common te开发者_StackOverflow中文版rms.

DECLARE @Table1 TABLE (Term1 int)
INSERT INTO @Table1 VALUES (1),(2),(5),(5),(5),(6),(7),(8)
DECLARE @Table2 TABLE (Term2 int)
INSERT INTO @Table2 VALUES (3),(4),(5),(5),(5),(6),(9),(0)

;WITH T1 AS
(
SELECT Term1, ROW_NUMBER() OVER (PARTITION BY Term1 ORDER BY (SELECT 0)) RN1
FROM @Table1
),
T2 AS
(
SELECT Term2, ROW_NUMBER() OVER (PARTITION BY Term2 ORDER BY (SELECT 0)) RN2
FROM @Table2
)
SELECT T1.Term1
FROM T1 JOIN T2 ON T1.Term1=T2.Term2 AND T1.RN1=T2.RN2

Output

Term1
-----------
5
5
5
6