如何用一条MySQL命令统计两张表行数并按日期分组？

嘿，这个问题我太熟了！你碰到的1241 - Operand should contain 1 column(s)错误，根源是你写的子查询返回了两列数据（date(date)和COUNT(sh_sh)），但SQL要求SELECT子句里的每个字段只能对应单个值，数据库自然就报错啦😅

当然可以用单条SQL实现你想要的效果——核心思路是先分别对两个表做日期分组统计，再把两个统计结果按日期关联起来，就能得到同一行显示对应日期的两个计数。

方案一：用全外连接（支持的数据库如PostgreSQL、SQL Server） #

如果你的数据库支持FULL OUTER JOIN，可以直接把两个统计结果按日期拼接：

SELECT 
    COALESCE(u.date, t.date) AS common_date,
    u.url_count AS count_sh_sh,
    t.ip_count AS count_ip
FROM
    (SELECT DATE(date) AS date, COUNT(sh_sh) AS url_count 
     FROM sh_url 
     GROUP BY DATE(date)) u
FULL OUTER JOIN
    (SELECT DATE(date) AS date, COUNT(ip) AS ip_count 
     FROM tracking 
     GROUP BY DATE(date)) t
ON u.date = t.date
ORDER BY common_date;

• COALESCE(u.date, t.date)用来统一日期列，不管哪个表有这个日期都会显示
• 某个日期如果只有其中一个表有数据，另一个计数会显示NULL

方案二：兼容MySQL等不支持全外连接的数据库 #

如果用的是MySQL这类不支持FULL OUTER JOIN的数据库，可以先通过UNION获取所有存在的日期，再左连接两个统计结果：

SELECT 
    dates.common_date,
    COALESCE(u.url_count, 0) AS count_sh_sh,
    COALESCE(t.ip_count, 0) AS count_ip
FROM
    (SELECT DATE(date) AS common_date FROM sh_url 
     UNION 
     SELECT DATE(date) AS common_date FROM tracking) dates
LEFT JOIN
    (SELECT DATE(date) AS date, COUNT(sh_sh) AS url_count 
     FROM sh_url 
     GROUP BY DATE(date)) u ON dates.common_date = u.date
LEFT JOIN
    (SELECT DATE(date) AS date, COUNT(ip) AS ip_count 
     FROM tracking 
     GROUP BY DATE(date)) t ON dates.common_date = t.date
ORDER BY dates.common_date;

这里用COALESCE(..., 0)把NULL替换成0，让结果更规整，完全符合你期望的输出格式。

内容的提问来源于stack exchange，提问作者Jazuly