当前位置：首页 > article >正文

一个复杂的SQL分析

article 2025/1/6 4:03:56

在这里插入图片描述
今天公司导出数据用到了一个看着非常复杂的Sql，正好来分析一下

导出手动添加的人脉分析

前提分析

先看用到的表：

connections_batch_relation：批量添加人脉表
connections_company_initialization：公司初始化任务表
connections_my_relation：我的人脉表
connections_task_data：数据处理任务表
user：用户信息表
winlabel_company：企业信息表
然后将公司初始化任务表中的uid（主账号用户id）都取出来，作为下面sql中@abc变量的值

然后进行sql查询：

 SET @abc = '200001720,200000161,200000348,200000162,200001986,200000164,200002219,200001983,200000651,200000808,200002217,200000328,200000809,200001774,200002582,200001850,200002631,200002337,200002516,200000493,200000293,200002163,200001772,200002720,200002718,200002733,200002736,200002740,200002753,200002819,200000890,200002834,200002825,200002039,200000423,200002734,200002964,200002985,200002677,200002998,200002975,200002480,200001992,200003070,200001854,200003172,200003178,200002010,200003217,200002465,200001721,200002777,200003257,200003159,200003326,200003398,200003365,200002021,200003491,200003573,200003580,200003465,200003409,200003622,200000789,200003633,200001758,200001532,200003660,200002986,200003675,200003677,200003679,200002935,200003705,200003712,200003715,200001745,200003787,200003825,200001497,200003884,200003912,200003919,200003982,200003983,200003994,200004002,200004019,200004048,200004063,200004082,200003724,200004098,200004101,200004148,200004020,200004247,200004294,200004295,200004300,200003768,200002026,200004440,200004406,200004450,200004463,200004506,200004530,200004697,200004470,200001786,200001870,200004498,200002603,200004532,200004727,200003619,200004852,200002896,200004872,200001755,200004720,200002147,200004928,200004369,200004935,200004936,200002595,200002342,200004972,200004989,200004990,200004994,200005006';
 
 -- SELECT DISTINCT temp2.pid FROM (
 
 SELECT
 IF(temp.`企业名称` is not null,temp.`企业名称`, (SELECT company_name FROM connections_company_initialization WHERE user_id IN (SELECT IF(parent_id = 0,id,parent_id) as 'pid' FROM `user` WHERE id = temp.uid))) AS '企业名称',
 (SELECT IF(parent_id = 0,id,parent_id) as 'pid' FROM `user` WHERE id = temp.uid) as 'pid',
 
 NULL AS '数量',
 
 temp.`uid`,
 temp.`添加人脉企业`,
 temp.`类型`,
 temp.`添加时间`,
 (
 SELECT EXISTS
 ( SELECT * FROM connections_my_relation WHERE user_id = temp.uid AND company_name = temp.`添加人脉企业` )) AS '是否删除' ,
 (SELECT phone FROM `user` WHERE id = temp.uid) as '手机号',
 (SELECT contact_name FROM `user` WHERE id = temp.uid) as '姓名',
 (SELECT CASE
 WHEN user_comment is null THEN
 ''
 WHEN user_comment = 0 THEN
 '过期用户'
 WHEN user_comment = 1 THEN
 '新用户'
 WHEN user_comment = 2 THEN
 '内部用户'
 WHEN user_comment = 3 THEN
 '试用账户'
 WHEN user_comment = 4 THEN
 '成交会员'
 END AS userComment
 FROM `user` WHERE id = temp.uid) as '用户备注',
 (SELECT w.company_name FROM `user` u LEFT JOIN winlabel_company w ON u.company_id = w.id WHERE u.id = temp.uid) as '用户注册公司名称'
 
 FROM
 (
 SELECT
 null AS '企业名称',
 tt2.user_id AS 'uid',
 tt1.`name` AS '添加人脉企业',
 (SELECT company_type FROM connections_my_relation mr WHERE mr.user_id = tt2.user_id AND mr.company_name = tt1.`name`) AS '类型',
 tt1.create_time AS '添加时间'
 FROM
 (
 SELECT
 `name`,
 batch_id,
 user_id,
 create_time
 FROM
 connections_task_data
 WHERE
 join_type = 2
 AND user_id IN (SELECT id FROM `user` WHERE deleted = 0 AND (FIND_IN_SET(id,@abc) OR FIND_IN_SET(parent_id,@abc)))
 ) tt1
 LEFT JOIN connections_batch_relation tt2 ON tt1.batch_id = tt2.batch_id
 
 
 UNION
 SELECT
 null AS '企业名称',
 user_id AS 'uid',
 company_name AS '添加人脉企业',
 company_type AS '类型',
 create_time AS '添加时间'
 FROM
 connections_my_relation
 WHERE
 user_id IN (SELECT id FROM `user` WHERE deleted = 0 AND (FIND_IN_SET(id,@abc) OR FIND_IN_SET(parent_id,@abc)))
 AND batch_id != '预制数据'
 AND CONCAT( company_name, user_id ) NOT IN
 
 (
 SELECT
 CONCAT(tt1.`name`,tt2.user_id)
 FROM
 (
 SELECT
 `name`,
 batch_id,
 user_id,
 create_time
 FROM
 connections_task_data
 WHERE
 join_type = 2
 AND user_id IN (SELECT id FROM `user` WHERE deleted = 0 AND (FIND_IN_SET(id,@abc) OR FIND_IN_SET(parent_id,@abc)))
 ) tt1
 LEFT JOIN connections_batch_relation tt2 ON tt1.batch_id = tt2.batch_id
 )
 ) temp
 
 WHERE temp.`添加时间` >= '2024-02-19 00:00:00'
 
 ORDER BY '企业名称' ASC
 
 -- ) temp2 ORDER BY temp2.pid

先将结果折叠看一下整体的语句，不难发现是对temp表的一次查询
在这里插入图片描述
所以sql的整体框架是从temp表中查询

temp是什么：展开折叠发现是由两个子查询UNION起来的

第一个子查询

第一个子查询是对tt1和tt2表的查询，其中tt1又是一个子查询、tt2是批量添加人脉表，所以先看tt1

第一个子查询的子查询（tt1）：

SELECT
    `name`,
    batch_id,
    user_id,
    create_time 
FROM
    connections_task_data 
WHERE
    join_type = 2 
    AND user_id IN (
    SELECT
        id 
    FROM
        `user` 
    WHERE
        deleted = 0 
        AND (
            FIND_IN_SET( id, @abc ) 
        OR FIND_IN_SET( parent_id, @abc ))) 
) tt1

从数据处理任务表中查询公司名称、批次id、主账号uid、创建时间，限制条件：不展示直接人脉数据的、主账号uid必须是未删除并且uid或者父uid在上面的给出的id中，然后来看第一个子查询：

SELECT NULL AS
    '企业名称',
    tt2.user_id AS 'uid',
    tt1.`name` AS '添加人脉企业',
    ( SELECT company_type FROM connections_my_relation mr WHERE mr.user_id = tt2.user_id AND mr.company_name = tt1.`name` ) AS '类型',
    tt1.create_time AS '添加时间' 
FROM
    (
    SELECT
        `name`,
        batch_id,
        user_id,
        create_time 
    FROM
        connections_task_data 
    WHERE
        join_type = 2 
        AND user_id IN (
        SELECT
            id 
        FROM
            `user` 
        WHERE
            deleted = 0 
            AND (
                FIND_IN_SET( id, @abc ) 
            OR FIND_IN_SET( parent_id, @abc ))) 
    ) tt1
    LEFT JOIN connections_batch_relation tt2 ON tt1.batch_id = tt2.batch_id

第一个子查询主要是对tt1表中的数据封装，连接tt2表做为补充，封装的字段是企业名称、uid、添加人脉企业、类型、添加时间

所以第一个子查询大概意思是从数据处理任务表中找到对应用户的人脉数据

第二个子查询

SELECT NULL AS
    '企业名称',
    user_id AS 'uid',
    company_name AS '添加人脉企业',
    company_type AS '类型',
    create_time AS '添加时间' 
FROM
    connections_my_relation 
WHERE
    user_id IN (
    SELECT
        id 
    FROM
        `user` 
    WHERE
        deleted = 0 
        AND (
            FIND_IN_SET( id, @abc ) 
        OR FIND_IN_SET( parent_id, @abc ))) 
    AND batch_id != '预制数据' 
    AND CONCAT( company_name, user_id ) NOT IN (
    SELECT
        CONCAT( tt1.`name`, tt2.user_id ) 
    FROM
        (
        SELECT
            `name`,
            batch_id,
            user_id,
            create_time 
        FROM
            connections_task_data 
        WHERE
            join_type = 2 
            AND user_id IN (
            SELECT
                id 
            FROM
                `user` 
            WHERE
                deleted = 0 
                AND (
                    FIND_IN_SET( id, @abc ) 
                OR FIND_IN_SET( parent_id, @abc )))  
        ) tt1
        LEFT JOIN connections_batch_relation tt2 ON tt1.batch_id = tt2.batch_id

意思是从我的人脉表中查询uid、添加的人脉企业、类型人脉公司类型、添加时间、同样限制主账号uid必须是未删除并且uid或者父uid在上面的给出的id中，并且批次id不能为预制数据，并且uid和添加的人脉企业组成的唯一不能出现在数据处理任务表中这些不展示直接人脉数据和对应的uid中，同样最后和批量添加人脉表通过批次id联合起来来辅助

两个子查询的字段都相同，所以UNION的结果是将它们合并成一个结果集，自动去除重复的数据

所以temp的临时表就是（运行结果）：
在这里插入图片描述
最后外面的一层查询就相当于在temp表中操作，返回想要的字段