mysql联查中使用if和group by会让你的结果不是你想要的
2024-08-31 12:15:46
mysql中的if语句遇到统计count group by的时候会出现不准确的情况，原因是分组后if条件的结果以第一条为准，不会跟着分组
例如：
SELECT t1.*,t2.nick_name,t2.avatar,IF(t1.user_id=104080,1,0) AS is_owner,
IF(t4.user_id=104080,t4.vote_val,'N') AS is_voted,
IF(t7.user_id=104080,1,0) AS is_collected
FROM answer t1
JOIN user t2 ON t1.user_id = t2.id
LEFT JOIN answer_vote t4 ON t4.item_id = t1.id
LEFT JOIN answer_collection t7 ON t7.item_id = t1.id
HAVING approved = 1 AND question_id = 100134
ORDER BY id desc LIMIT 10 OFFSET 0;
#加入group by
SELECT t1.*,t2.nick_name,t2.avatar,IF(t1.user_id=104080,1,0) AS is_owner,
IF(t4.user_id=104080,t4.vote_val,'N') AS is_voted,
IF(t7.user_id=104080,1,0) AS is_collected
FROM answer t1
JOIN user t2 ON t1.user_id = t2.id
LEFT JOIN answer_vote t4 ON t4.item_id = t1.id
LEFT JOIN answer_collection t7 ON t7.item_id = t1.id
GROUP BY id HAVING approved = 1 AND question_id = 100134
ORDER BY id desc LIMIT 10 OFFSET 0;
最新文章
- netfilter分析
- [原] blade中C++ singleton的实现
- Oracle ->> 随机函数
- codevs 2800 送外卖（状压dp）
- 解决Virtual Box 安装Mac OS X当出现“hfs: summary table not allowed on FS with block size of 2048”问题
- UVALive - 3263 That Nice Euler Circuit （几何）
- datagridcolumn单元格怎么显示查询到的某个表的字段值（字段值可能为多个）
- D3.js：力导向图
- Java自己动手写连接池一
- POJ-2184 Cow Exhibition---01背包变形（负数偏移）
- vtigercrm特色功能介绍
- 将RAC软件转换为单实例软件
- ie请求缓存问题，页面内容没有及时更新
- BodeAbp概述
- UniConnectDialog使用
- 【洛谷p1403 】【AHOI2005】约数研究
- 互斥锁pthread_mutex_init()函数
- OpenACC 简单的直方图
- XML学习（1）
- git失败案例