Find Rows where the Same Two Column Values Recur

问题:

Given a table in SQL-Server like:

Id INTEGER
A  VARCHAR(50)
B  VARCHAR(50)
-- Some other columns

with no index on A or B, I wish to find rows where a unique combination of A and B occurs more than once.

I'm using the query

SELECT A+B, Count(A+B) FROM MyTable
GROUP BY A+B
HAVING COUNT(A+B) > 1

First Question

Is there a more time-efficient way to do this? (I cannot add indices to the database)

Second Question

When I attempt to gain some formatting of the output by including a , in the concatenation:

SELECT A+','+B, Count(A+','+B) FROM MyTable
GROUP BY A+','+B
HAVING COUNT(A+','+B) > 1

The query fails with the error

Column 'MyDB.dbo.MyTable.A' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

with a similar error for Column B.

How can I format the output to separate the two columns?

回答1:

It would seem more natural to me to write:

SELECT A, B, Count(*) FROM MyTable
GROUP BY A, B
HAVING COUNT(*) > 1

And it's the most efficient way of doing it (and so is the query in the question).

Similarly to the above query, you can rewrite your second query:

SELECT A + ',' + B, Count(*) FROM MyTable
GROUP BY A, B
HAVING COUNT(*) > 1