php - mysql连表统计查询

Question

活动表 rehearse
id(自增id),name(活动名称),send_time(开始时间),uid（创建活动的用户id）…
统计表 statis
id(自增id),mtype(活动类型1，2，3；1表示表rehearse内的活动)，sid(来源id,活动表的id),pid(人员id),open_time（打开邮件时间）…

高洛峰 · Answer

SELECT
    R.name,
    R.send_time,
    COUNT(DISTINCT S1.id) send_times,
    COUNT(DISTINCT S2.id) open_times
FROM rehearse R
LEFT JOIN
    statis S1
ON
    R.id = S1.sid AND S1.mtype = 1
LEFT JOIN
    statis S2
ON
    R.id = S2.sid AND S2.mtype = 1 AND open_time > 0
WHERE
    R.uid = 6
GROUP BY
    R.id

由于左联了两次，可能会有效率问题，在找更优方法

Update

方法2：子查询

SELECT
    R.name,
    R.send_time,
    COUNT(S.id) send_times,
    (SELECT COUNT(S.id) FROM S WHERE AND S.open_time > 0) open_times
FROM
    rehearse R
LEFT JOIN
    (SELECT * FROM statis WHERE mtype = 1 GROUP BY sid, open_time AND sid = R.id) S