php - pertanyaan statistik jadual bersambung mysql

Question

Id latihan jadual aktiviti (id penambahan sendiri), nama (nama aktiviti), masa hantar (masa mula), uid (ID pengguna yang mencipta aktiviti)...id status jadual statistik (id kenaikan sendiri), mtype (jenis aktiviti 1, 2, 3 ; 1 mewakili aktiviti dalam jadual latihan), sid (id sumber, id jadual aktiviti), pid (id orang), o...

高洛峰 · 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

Memandangkan pautan kiri disambungkan dua kali, mungkin ada masalah kecekapan saya sedang mencari cara yang lebih baik

Kemas kini

Kaedah 2: Subkueri

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