php - mysql connected table statistical query

Question

Activity table rehearse
id (self-incrementing id), name (activity name), send_time (start time), uid (user ID that created the activity)...
Statistical table status
id (self-incrementing id), mtype (activity type 1, 2, 3; 1 represents the activity in the table rehearse), sid (source id, id of the activity table), pid (person id), open_time (open mail time)...

Query the activity list created by the user with user ID 6 (including information: activity name, start time, total number of emails, total number of opened emails)
open_time>0 is the record of open emails, fixed condition mtype =1,sid= The active table id queried from rehearse through uid=6.
Now the query is performed separately, and then the array is processed.
I don’t know how to check the required data in a sql. Ask God for help

Answer 1

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

Since the left link is connected twice, there may be efficiency issues. I am looking for a better way

Update

Method 2: Subquery

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