MySQL查询同一列中具有相同第三个值的2个值并返回计数

Question

我正在尝试构建一个使用日志查询，计算两个包同时用于一个 uploadID 的次数。换句话说，我想知道每个 uploadID 出现了多少次 2 packages。这是我将数据存储在 MySQL 中的表的示例。

uploadID  package  
1         value1
1         value2
1         value3

2         value2
2         value3
2         value4

3         value1
3         value3
3         value4

4         value1
4         value3
4         value4

我正在寻找的输出是这样的:

Concurrent Packages    Count
value1 & value2          1
value1 & value3          3
value1 & value4          2
value2 & value3          2
value2 & value4          1  
value3 & value4          3

我已经尝试了很多查询来实现这一点，但似乎没有任何效果。理想情况下，一旦我得到计数​​，我想将输出放入矩阵中，如下所示:

        value1  value2  value3  value4
value1    -       1       3       2
value2    1       -       2       1     
value3    3       2       -       3
value4    2       1       3       -

对于如何实现这一目标的任何建议，我将不胜感激。数据存储在 MySQL 数据库中，但最终矩阵将在 PHP Web 应用程序中呈现。提前致谢。

Answer 1

第一个版本保持简单，这应该可以满足您的需求；

SELECT CONCAT(a.package, ' & ', b.package) "Concurrent Packages",
       COUNT(*) "Count"
FROM packages a
CROSS JOIN packages b
WHERE a.package<b.package AND a.uploadid=b.uploadid
GROUP BY a.package,b.package
ORDER BY a.package,b.package

SQLFiddle here .

至于第二个版本，可以用 MySQL 完成，但解决方案变得相当复杂。如果你想尝试，这里有info here这会让你开始。

查询的简短说明；表与其自身之间的 CROSS JOIN 将返回所有行组合。我们过滤掉，这样我们只计算值按字母顺序排列且 uploadid 在 WHERE 中匹配的值(字母顺序只是为了避免 a 匹配 b 和 b 匹配 a 计为两个单独的匹配项)。之后，我们COUNT 行数，GROUP BY 值，这样我们就可以得到每个组合的计数。