LEFT JOIN个别问题

最新推荐文章于 2023-08-29 11:00:10 发布

weixin_30628077

最新推荐文章于 2023-08-29 11:00:10 发布

阅读量83

点赞数

原文链接：http://www.cnblogs.com/wutianfei/p/10238205.html

版权

SELECT
a.loginuser,
a.schoolid,
count(b.id)
FROM
vhs_school AS a
LEFT JOIN vhs_attence AS b ON a.schoolid = b.schoolid
WHERE
b.recordtime > 1546791470
AND b.recordtime < 1546874270
GROUP BY
a.schoolid

与

SELECT
a.loginuser,
a.schoolid,
count(b.id)
FROM
vhs_school AS a
LEFT JOIN vhs_attence AS b ON a.schoolid = b.schoolid
AND b.recordtime > 1546791470
AND b.recordtime < 1546874270
GROUP BY
a.schoolid

前者就算用了left join 但是结果还是没有显示a表中的所有字段，因为b表的筛选条件放到最外层，这样就相当于将a表关联b表又做了一遍筛选,所有丢失了a表中其他的字段

后者已经筛选b表的数据后，数据量会减少，再去关联a

后者也可以用

SELECT
a.loginuser,
a.schoolid,
count(b.id)
FROM
vhs_school AS a
LEFT JOIN (
SELECT
*
FROM
vhs_attence AS b b.recordtime > 1546791470
AND b.recordtime < 1546874270
) b ON a.schoolid = b.schoolid

转载于:https://www.cnblogs.com/wutianfei/p/10238205.html