按X或Y分组？ [英] Group by X or Y?

问题描述

我正在尝试弄清楚如何在多个列上 GROUP BY 。我想在SSN 或地址匹配时对项目进行分组。例如，以下是三个记录：

  account_number |名称| ssn |地址
 --------------- + -------------- ++ ------------ + ---------------------- 
 23952352340 | SMITH INC | 123-45-6789 |邮局BOX 123 
 3459450340 |约翰·史密斯| 123-45-6789 | 123 EVERGREEN TERRACE 
 45949459494 |简·史密斯| 395-23-1924 | 123 EVERGREEN TERRACE 
  

这就是我要结束的地方：

 名称
 ---------------------- 
 SMITH INC，JOHN SMITH，JANE SMITH 
  

有任何建议吗？

解决方案

您无法在 MySQL 中轻松做到这一点。

<问题是您定义的关系类似于不是传递的。在您的示例中， Smith Inc 类似于 John Smith （每个 SSN ）和 John Smith 类似于 Jane Smith （每个名称），但 Smith Inc 与 Jane Smith 不同。因此，没有可以与所有记录进行比较的单一值，并且 GROUP BY 在这里无济于事。

在其他支持递归的系统中，您可以建立此关系的传递闭包以允许分组，但这在 MySQL 中并非易事。

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?

解决方案

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.

这篇关于按X或Y分组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！