mark: 写了一个复杂的sql

SELECT ftable.pid, ftable.sid, fcnt, mcnt, fcnt + mcnt AS tcnt
FROM (

SELECT main.pid, main.sid, COALESCE( main.cnt + vote_res.cnt, 0 ) AS fcnt
FROM (

SELECT pictures . id AS pid , style . id AS sid , 0 AS cnt
FROM pictures , style

) AS main
LEFT JOIN (

SELECT pid , sid , count ( * ) AS cnt
FROM vote , users
WHERE users . id = vote . uid
AND users . gender = 0
GROUP BY pid , sid

) AS vote_res ON main.pid = vote_res.pid
AND main.sid = vote_res.sid

) AS ftable, (

SELECT main . pid , main . sid , COALESCE ( main . cnt + vote_res . cnt , 0 ) AS mcnt
FROM (

SELECT pictures . id AS pid , style . id AS sid , 0 AS cnt
FROM pictures , style

) AS main
LEFT JOIN (

SELECT pid , sid , count ( * ) AS cnt
FROM vote , users
WHERE users . id = vote . uid
AND users . gender = 1
GROUP BY pid , sid

) AS vote_res ON main . pid = vote_res . pid
AND main . sid = vote_res . sid

) AS mtable
WHERE ftable.pid = mtable.pid
AND ftable.sid = mtable.sid

 

 

先mark下，稍后做解释