mark: 写了一个复杂的sql

本文介绍了一种复杂的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下,稍后做解释

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值