假设access_log表有src_ip、user_agent、url三个字段,每一次请求产生一条记录(行),现在想统计访问量前10的IP、访问量count及访问量占比percent
第一种方式:直接从多个表查询数据
SELECT
a.src_ip,
a.count,
round(a.count * 100 / b.total, 2) as percent
FROM
(
SELECT
src_ip,
count() as count
FROM
access_log
GROUP BY
src_ip
ORDER BY
count DESC
LIMIT
10
) a,(
SELECT
count() as total
FROM
access_log
) b
第二种方式:子查询作为分母
SELECT
src_ip,
count() as count,
round(
count * 100 /(
SELECT
count() as total
FROM
access_log
),
2
) as percent
FROM
access_log
GROUP BY
src_ip
ORDER BY
count DESC
LIMIT
10