SQL实战——值得研究的一句SQL
一、SQL内容
最近在开发过程中发现了这样一句sql，感觉很有研究价值，因此记录了下来。
这句SQL涉及到的知识点有：连表查询LEFT JOIN、去重取并集、字段判空ifnull与求和等。
为了便于讲解，已尽可能进行了简化：
SELECT
user as 用户ID,
id as 身份证号,
classid as 班级,
sign_num AS 签到次数
FROM
(
SELECT
U.user,
U.id,
U.class_id AS classid,
ifnull(SN.sign_num, 0) + ifnull(SN2.sign_num, 0) sign_num
FROM
USER U
LEFT JOIN SIGN SN ON U.class_id = SN.classid
AND U.user = SN.id
LEFT JOIN SIGN SN2 ON U.class_id = SN2.classid
AND U.id = SN2.id
AND U.user != SN2.id
WHERE
1=1
AND U.class_id = 7
)ABC二、相关表结构
要分析SQL，首先要弄清楚相关表结构。
这句SQL涉及到2个表：
1.表名user，用来存储用户信息；字段为user（主键）、id、class_id，分别是用户账号、身份证号、班级号；
其中的数据如下图：
2.表名sign，用来存储签到次数；字段为id(主键)、sign_num、classid，分别是id、签到次数、班级号；
其中的数据如下图：
三、业务场景与需要注意的点
可能只看上文还没有明白这句sql要做什么，下面就来详细讲解。
首先，这是一个记录用户签到次数的功能模块，user表存储着用户基本信息，有账号（user）、身份证号（id）、班级号（class_id）；到这里应该没什么问题。
然后，sign表存储的是用户的签到次数，有id、签到次数（sign_num）、班级号（classid）。
最后，上方的SQL的作用是连表查询，找出每个用户的签到次数(sign_num)。
其中，需要注意的点有：
--------------------------------------------------------------------------------------------------------
1.有的用户用账号签到，例如用户a；user.user=sign.id，签到5次；
有的用户用身份证号签到，例如用户b；user.id=sign.id，签到5次；
有的用户两种签到方式都用，例如用户c；user.user=sign.id时签到2次，user.id=sign.id，时签到3次，合计签到也是5次。
这样就导致user表与sign表的对应方式分两种情况，并且还要考虑到用户两种签到方式都用时、要将签到次数相加。
2.在user表中，有的用户的账号与身份证号相同，例如用户1，user.user=user.id；
有的用户的账号与身份证号不同，例如其它用户。
这样就导致连表查询时需要去重。（对应SQL中的【AND U.user != SN2.id】）
--------------------------------------------------------------------------------------------------------
四、SQL讲解
SQL执行后的结果如下图：
连表查询到了每个用户的签到次数。
因为有步骤三中需要注意的点，所以SQL有些复杂，核心部分如下（从上方抄下来一些）：
SELECT
U.user,
U.id,
U.class_id AS classid,
ifnull(SN.sign_num, 0) + ifnull(SN2.sign_num, 0) sign_num
FROM
USER U
LEFT JOIN SIGN SN ON U.class_id = SN.classid
AND U.user = SN.id
LEFT JOIN SIGN SN2 ON U.class_id = SN2.classid
AND U.id = SN2.id
AND U.user != SN2.id 1.ifnull(SN.sign_num, 0)的作用是，当查到的字段为空时，用0做结果，不为空时，用查到的值。
2.第一个LEFT JOIN查询出来的，是用户使用账号签到时的签到次数，为【SN.sign_num】；
3.第二个LEFT JOIN查询出来的，是用户使用身份证号时的签到次数，为【SN2.sign_num】；由于部分用户的账号与身份证号相同（例如用户1），这部分用户在第一个LEFT JOIN中就已经查询出来签到次数了，如果不去重的话就会多查询出来一次，得到错误的数据，因此要去重，使用【AND U.user!=SN2.id】
4.最后将这两种签到次数相加，就得到了每个用户正确的签到次数【ifnull(SN.sign_num, 0) + ifnull(SN2.sign_num, 0) sign_num 】。
五、总结
由于业务逻辑比较复杂（见第三点），导致SQL比较复杂，需要left join两次联查同一个表并对结果求和、需要去重取并集；
本人到现在还是感觉有点晕，如果大家没看懂本文，可以留言交流，谢谢！
