mysql - 不同条件的 SQL SELECT COUNT
问题描述
我有一个包含以下示例数据的表格
IP URL
========= ========
1.1.1.1 aaaa
2.2.2.2 bbbb
1.1.1.1 ffff
1.1.1.1 aaaa
3.3.3.3 ssss
5.5.5.5 aaaa
7.7.7.7 bbbb
4.4.4.4 aaaa
1.1.1.1 aaaa
3.3.3.3 bbbb
5.5.5.5 aaaa
我需要选择这个结果:
IP URL Total count of related IPs Total count of current IP for current URL
(DESC SORT)
======= ===== ============================= ===========================================
1.1.1.1 aaaa 6 3
5.5.5.5 aaaa 6 2
4.4.4.4 aaaa 6 1
2.2.2.2 bbbb 3 1
7.7.7.7 bbbb 3 1
3.3.3.3 bbbb 3 1
1.1.1.1 ffff 1 1
3.3.3.3 ssss 1 1
我使用了一些类似以下的查询,但没有成功获得确切的结果:
SELECT ip, url, COUNT(ip) totalip FROM mytable
GROUP BY url ORDER BY totalip DESC
感谢任何帮助
解决方案
对于 MySql 8.0+,您可以使用 COUNT() 窗口函数:
SELECT DISTINCT IP, URL,
COUNT(*) OVER (PARTITION BY URL) Total_count_of_related_IPs,
COUNT(*) OVER (PARTITION BY IP, URL) Total_count_of_current_IP_for_current_URL
FROM tablename
ORDER BY Total_count_of_related_IPs DESC
对于以前的版本,使用自联接和聚合:
SELECT t1.IP, t1.URL,
COUNT(*) Total_count_of_related_IPs,
SUM(t1.IP = t2.IP) Total_count_of_current_IP_for_current_URL
FROM (SELECT DISTINCT * FROM tablename) t1 INNER JOIN tablename t2
ON t2.URL = t1.URL
GROUP BY t1.IP, t1.URL
ORDER BY Total_count_of_related_IPs DESC
见演示。
推荐阅读
- javascript - 从 jsx prop 重构箭头函数
- mysql - SQL在有条件的日期之间查找行
- apache-spark - Apache Spark 工作节点可以是不同于 HDFS 数据节点的机器吗?
- python - 错误“索引超出范围”但不是超出范围 PIL IMAGE CREATE
- sql - 在引用外部表时从子查询加入 TOP 1
- ios - 如何从 iOS 应用通知设置重定向到应用通知设置
- python - Python Y 轴刻度归一化
- c++ - 强大的兼容性检查?
- regex - 在TextPad中以递增顺序替换数字的正则表达式
- charts - 如何从 chart.js 中删除值