这个坑可能只是在有类似需求的时候会出现的,希望能帮到各位。
我在写项目的数据分析查询时使用mysql子查询发现一个子查询的缺点,也就是如果子查询里再嵌套一个子查询就有问题。
mysql版本是5.7,我的查询语句如下,主要是查询时间范围内项目每天的人员分布情况
- select i.all_day, a.project_id, p.name,
- (select count(ac.assigned_to_id) from (select fi.assigned_to_id from issues fi where fi.project_id = a.project_id and i.all_day >= fi.start_date and fi.due_date >= i.all_day GROUP BY fi.assigned_to_id) ac) AS user_count
- from (SELECT @rownum:=@rownum+1 AS row_no, DATE_ADD('2018-06-01', INTERVAL @rownum DAY) AS all_day
- FROM (SELECT @rownum:=-1) r, issues) i
- LEFT JOIN issues a ON (i.all_day = DATE(a.start_date))
- left join projects p on a.project_id = p.id
- where i.all_day between '2018-09-01' and '2019-01-01'
- GROUP BY i.all_day, a.project_id
- order by i.all_day asc
上面红色的子查询语句把字段替换成固定值后查询是不报错的,但是用以上语句查询会保错
[Err] 1054 - Unknown column 'a.project_id' in 'where clause'
出错的原因是在于因为列子查询里再进行一次子查询,而导致最里面的一层子查询引用不到外面的 a.project_id列的值(我一开始以为是字段错了之类的,但是经过试验发现不是)
后面查了一些资料,也可以用group_concat函数去实现,但是那样比较麻烦,后面继续查了很多资料,最后使用了count函数和distinct关键字来实现,最后的查询代码如下
- select i.all_day, a.project_id, p.name,
- (select count(DISTINCT assigned_to_id) from issues where project_id = a.project_id and i.all_day >= start_date and due_date >= i.all_day) AS user_count
- from (SELECT @rownum:=@rownum+1 AS row_no, DATE_ADD('2018-06-01', INTERVAL @rownum DAY) AS all_day
- FROM (SELECT @rownum:=-1) r, issues) i
- LEFT JOIN issues a ON (i.all_day = DATE(a.start_date))
- left join projects p on a.project_id = p.id
- where i.all_day between '2018-09-01' and '2019-01-01'
- GROUP BY i.all_day, a.project_id
- order by i.all_day asc
distinct是消除该字段重复值,和group查询处理的结果有点类似,最后用count函数统计总行数。搞定!
这次是本人第一次写博客,以上有什么说得不对的或者有什么改进的可以在博客下方留言,希望能给帮助到有相同问题的人!