创建几个列的所有组大小2(Create all sets size 2 of several columns)

我使用的是Maria DB 10.1.16，我正在寻找几个列中最常出现的集合大小2。

示例数据库：

ID | Col 1 | Col 2 | Col 3 | Result ID | EXPECTED RESULT 1 1 0 2 1 "Col 1/ Col 3" 2 1 0 0 No Results 3 1 1 3 3 "Col 1/ Col 2" 3 "Col 1/ Col 3" 3 "Col 2/ Col 3"

通过这个新专栏，我只需要Count和GroupBy来接收哪一组会发生的频率。

然而，我不太确定转移它的优雅方式。基线解决方案将检查所有可能集的存在的每一行：

SELECT *,"Col 1/ Col 2" AS FreqSet FROM `Table` WHERE Col 1 >0 AND Col 2 >0 UNION SELECT *,"Col 1/ Col 3" AS FreqSet FROM `Table` WHERE Col 1 >0 AND Col 3 >0 UNION SELECT *,"Col 2/ Col 3" AS FreqSet FROM `Table` WHERE Col 2 >0 AND Col 3 >0 Order By ID

然而，因为我想这样做总共8列，结果将是28个SELECT语句来涵盖所有组合。所以我希望有更好的解决方案^^。

附注我还有另一个视图，其中包含所有条目> 0，如下所示：

ID ColTotal 1 Col1 1 Col3 2 Col1 3 Col1 3 Col2 3 Col3

也许某人有更好的队列来观看该视图。我希望我已经覆盖了一切？

提前感谢您的任何提示！问候Hasorko

I'm using Maria DB 10.1.16 and I am looking for the most often occuring set size 2 of several columns.

Example Database:

ID | Col 1 | Col 2 | Col 3 | Result ID | EXPECTED RESULT 1 1 0 2 1 "Col 1/ Col 3" 2 1 0 0 No Results 3 1 1 3 3 "Col 1/ Col 2" 3 "Col 1/ Col 3" 3 "Col 2/ Col 3"

With this new column I'd just Count and GroupBy to receive how often which set would occur.

I am not quite sure about an elegant way to transfer this however. The baseline solution would be to check every row of the existence of all possible sets:

However as I want to do this for a total of 8 columns the result would be 28 SELECT statements to cover all combinations. So I am hoping for a better solution ^^.

Side note I also have another view containing all entries >0 in one column like this:

ID ColTotal 1 Col1 1 Col3 2 Col1 3 Col1 3 Col2 3 Col3

Maybe someone has a better queue for that view. I hope I have covered everything?

Thanks for any tips in advance! Greetings Hasorko