Analysis of a Complex SQL Query for Exporting Manually Added Contacts

Today the company used a very complex SQL for exporting data, so let’s analyze it.

Export manually added contacts analysis

Prerequisite analysis

First, look at the tables used:

connections_batch_relation: batch‑added contacts table

connections_company_initialization: company initialization task table

connections_my_relation: my contacts table

connections_task_data: data processing task table

user: user information table

winlabel_company: enterprise information table

Then the uid (main account user id) from the company initialization task table is extracted and assigned to the @abc variable.

Next, the SQL query is executed:

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 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

First, folding the result shows that the whole statement is a query on the temporary table temp .

Thus the overall framework is querying from the temp table.

temp is built by UNIONing two sub‑queries.

First sub‑query

The first sub‑query joins tt1 (a derived table from connections_task_data ) with tt2 (the batch‑added contacts table).

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)))

This extracts company name, batch id, main account uid, and creation time, limited to non‑deleted users whose uid or parent uid appears in the @abc list.

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

This sub‑query packages fields: company name (null placeholder), uid, added contact company, type, and addition time.

Second sub‑query

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
)

This extracts uid, added contact company, type, and addition time from the personal contacts table, applying the same uid filter, excluding the batch id "预制数据", and ensuring the combination of company name and uid does not appear in the task data.

Both sub‑queries share the same column list, so the UNION merges them while removing duplicates.

The outermost query then operates on temp , selecting the desired fields.

Author: CoderLiz Source: juejin.cn/post/7352100456334606346

Backend Exclusive Technical Group
Build a high‑quality technical exchange community, welcome developers, technical recruiters, and anyone willing to share internal job referrals for mutual help and progress!
Speak civilly, focusing on
technical exchange
,
job referrals
, and
industry discussion
.
Advertisers, please do not join; beware of private messages and scams.
Add me as a friend, I’ll invite you to the group.