#Artalk 根据邮箱名查询评论数量，同时列出最近一次录入数据库中的用户名以及链接地址。 #mysql
WITH latest_user AS (
SELECT
email,
name,
link,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) AS rn
FROM artalk.users
),
joined AS (
SELECT
a.user_id,
u.email,
lu.name AS latest_name,
lu.link AS latest_link
FROM artalk.comments a
JOIN artalk.users u ON a.user_id = u.id
JOIN latest_user lu ON u.email = lu.email AND lu.rn = 1
)
SELECT
email,
latest_name,
latest_link,
COUNT(*) AS record_count
FROM joined
GROUP BY email, latest_name, latest_link
ORDER BY record_count DESC;自从优化了 #mysql 配置信息后，网站已经保持 30 天 260 万秒在线了。
