SQL返回两列的所有组合及其出现的次数 [英] SQL Return All Combinations of Two Columns and Their Number of Occurences

问题描述

我正在尝试用SQL做一些我可能做不到的事情.我有一个带有这种类型的数据的表(显示了合成数据):

I'm trying to do something in SQL that maybe I can't do. I have a single table with this type of data (synthetic data shown):

columnn1 | column2 | ...
------------------------
    A    |    1    |
    B    |    1    |
    C    |    2    |
    A    |    2    |
    D    |    3    |
    A    |    1    |
    B    |    1    |

我想找回的东西都可以:

What I'd like to get back is something that both:

1. 计算所有组合(例如，交叉联接)，并且
2. 还包括组合发生的次数.

我知道如何通过简单的CROSS JOIN进行组合.我还可以通过一个简单的GROUP BY子句获得组合的数量.但是，有没有一种方法可以有效地将它们组合成一个查询?我想尝试避免的是生成并保存中间表.我想要的输出看起来像这样:

I know how to do the combinations with a simple CROSS JOIN. I can also get the number of combinations with a simple GROUP BY clause. However, is there a way to efficiently combine these into a single query? What I want to try to avoid is generating and saving an intermediate table. The output I'd like looks like this:

columnn1 | column2 | count
---------------------------
    A    |    1    |   2
    A    |    2    |   1
    A    |    3    |   0 (or null)
    B    |    1    |   2
    B    |    2    |   0 (or null)
    B    |    3    |   0 (or null)
    C    |    1    |   0 (or null)
    C    |    2    |   1
    C    |    3    |   0 (or null)
    D    |    1    |   0 (or null)
    D    |    2    |   0 (or null)
    D    |    3    |   1

我并不在乎计数是否为零(首选)或nullls.我也不在乎列的排序顺序.我已经进行了一些搜索，似乎无法找到一种无需生成并保存中间表的方法.我可能只是忽略了一些愚蠢的事情.预先感谢！

I don't really care if the counts are zero (preferred) or nullls. I also don't care about the sorting order of the columns. I've done some searching and can't seem to find a way to do this without generating and saving an intermediate table. I'm probably just overlooking something silly though. Thanks in advance!

推荐答案

假设表中的所有值都不为NULL，则可以采用以下策略.使用cross join获取两列的所有组合-甚至包括不在数据中的组合.

Assuming none of the values in the table are NULL, you can take the following strategy. Use cross join to get all the combinations of the two columns -- even combinations that are not in the data.

然后使用left join和group by来获取您要查找的计数:

Then use left join and group by to get the counts you are looking for:

select c1.col1, c2.col2, count(t.col1)
from (select distinct col1 from table) c1 cross join
     (select distinct col2 from table) c2 left join
     table t
     on t.col1 = c1.col1 and t.col2 = c2.col2
group by c1.col1, c2.col2;

处理NULL值非常简单，但是需要更多的逻辑.

Handling NULL values is pretty easy, but it requires a bit more logic.

这篇关于SQL返回两列的所有组合及其出现的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！