通过SQL枚举组合 [英] Enumerating combinations via SQL

问题描述

我有一个这样的表:

ID | name
=================================
 1 | apple
 1 | bear
 2 | cactus
 2 | dog
 2 | apple
 3 | apple
 3 | dog
==================================

我希望从SQL查询的输出中获得每个ID的以下唯一组合集:

and I wish to get the following unique set of combinations per ID as output from an SQL query:

===================
apple  | bear
apple  | cactus
apple  | dog
cactus | dog
===================

它本质上是一种转置.理想情况下，我想将其扩展为包含一些共同发生的次数，例如:

It's essentially a type of transpose. Ideally, I'd like to expand this to include a count of co-occurances, like this:

===================
1 | apple  | bear
1 | apple  | cactus
2 | apple  | dog
1 | cactus | dog
===================

但是这可能超越了.我知道我可以使用存储的proc完成上述所有操作；我很好奇我是否可以作为查询来做.

but this may be above and beyond. I know I can accomplish all of the above using a stored proc; was just curious if I can do this as a query.

任何SQL都可以，但是如果可能的话，首选MySQL.

Any SQL will do, but MySQL would be preferred if possible.

感谢您的所有输入！

推荐答案

在您的示例中，您似乎希望第一列包含按字母顺序排在首位的name，因此我在连接表时使用了<自己.

From your example, it looks like you want the first column to contain whatever name comes first alphabetically, so I used a < when joining the table to itself.

select count(*), t1.Name, t2.Name
from 
    tblThings t1 join
    tblThings t2 ON t1.ID = t2.ID AND t1.Name < t2.Name
group by t1.Name, t2.Name

这是正在执行的查询，用以证明我并不疯狂.

这篇关于通过SQL枚举组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！