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
高洛峰2017-06-24 09:43:26
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
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