mark: 写了一个复杂的sql

原创于 2009-02-17 10:55:00 发布 · 556 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#join #sql

闲得慌专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种复杂的SQL查询方法，该方法用于从多个表中选择数据并进行聚合操作，特别是通过LEFT JOIN结合子查询来实现对不同性别用户投票计数的统计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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下，稍后做解释