mysql - 如何获取多个表的项目计数

Question

我有多个表(每天一个)都具有相同的结构:ID、srcIP、时间。

我可以使用以下方法获取每天的总行数:

SELECT
  (SELECT COUNT(*) FROM Day1) as Day1Count, 
  (SELECT COUNT(*) FROM Day2) as Day2Count,
  (SELECT COUNT(*) FROM Day3) as Day3Count

但这只是给了我

Day1count  Day1count Day1count
102        148       131

但是我怎样才能得到每个 IP 地址每天的总数呢？并非所有 IP 每天都存在。

srcIP       D1  D2  D3
172.16.0.1      99  34
172.16.0.2  55      22
172.16.0.3  47  49  75

提前致谢!

Answer 1

要按 ip 和按天获取计数，最简单的方法是展平查询:

SELECT 'day1' AS day, srcIP, count(*) AS count FROM Day1 GROUP BY srcIP
UNION
SELECT 'day2' AS day, srcIP, count(*) AS count FROM Day2 GROUP BY srcIP
UNION
SELECT 'day3' AS day, srcIP, count(*) AS count FROM Day3 GROUP BY srcIP

然后在您的应用中转置它以获得您想要的表格格式。

或者

您也可以通过加入 IP 来完成:

SELECT srcIP, d1.count, d2.count, d3.count
FROM (SELECT srcIP, count(*) AS count FROM Day1 GROUP BY srcIP) d1
LEFT JOIN (SELECT srcIP, count(*) AS count FROM Day2 GROUP BY srcIP) d2 USING (srcIP)
LEFT JOIN (SELECT srcIP, count(*) AS count FROM Day3 GROUP BY srcIP) d3 USING (srcIP)

但是在这里您将丢失不在 Day1 中的 IP，除非您首先从所有天的 UNION 中执行 SELECT DISTINCT srcIP，这非常昂贵。基本上，这种表结构不太适合这种聚合。