Group by X or Y?

问题:

I'm trying to figure out how to GROUP BY on multiple columns. I want to group items when the SSN or the address matches. For example, here are three records:

account_number | name         | ssn         | address
---------------+--------------+-------------+----------------------
23952352340    | SMITH INC    | 123-45-6789 | P.O. BOX 123
3459450340     | JOHN SMITH   | 123-45-6789 | 123 EVERGREEN TERRACE
45949459494    | JANE SMITH   | 395-23-1924 | 123 EVERGREEN TERRACE

And here's what I'd like to end up with:

names
----------------------
SMITH INC, JOHN SMITH, JANE SMITH

Any suggestions?

回答1:

You can't do this easily in MySQL.

The problem is that the relation "is similar to" as you define it is not transitive. In your example, Smith Inc is similar to John Smith (per SSN) and John Smith is similar to Jane Smith (per name), but Smith Inc is not similar to Jane Smith. So there is no single value that all records could be compared with and GROUP BY won't help here.

In other systems which support recursion you could build a transitive closure of this relation which would allow grouping, but this is not an easy task in MySQL.

回答2:

Like this:

SELECT
    name,
    ssn,
    COUNT(*)
FROM TheTable
GROUP BY
    name,
    ssn