三级分销会员一次查询出来的SQL语句

最新推荐文章于 2021-04-09 09:29:50 发布

转载最新推荐文章于 2021-04-09 09:29:50 发布 · 1.4k 阅读

文章标签：

#php #数据库 #c#

本文介绍了一个复杂的SQL查询案例，通过递归方式获取组织结构中指定父节点的所有子节点信息，包括用户名称、城市、出生日期及电话号码等。此查询使用了UNION ALL联合多个相同结构的子查询实现多层次的数据检索。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT p.id AS partyId, p.parent_id AS parentId, pul.username AS userName, p.city, p.birth_date AS birthDate, p.phone AS telePhoneNo FROM party p LEFT JOIN party_user_login pul ON pul.party_id = p.id WHERE 1=1 AND p.parent_id = 57 union all SELECT p.id AS partyId, p.parent_id AS parentId, pul.username AS userName, p.city, p.birth_date AS birthDate, p.phone AS telePhoneNo FROM party p LEFT JOIN party_user_login pul ON pul.party_id = p.id WHERE 1=1 AND p.parent_id in ( SELECT partyId FROM (SELECT p.id AS partyId, p.parent_id AS parentId, pul.username AS userName, p.city, p.birth_date AS birthDate, p.phone AS telePhoneNo FROM party p LEFT JOIN party_user_login pul ON pul.party_id = p.id WHERE 1 = 1 AND p.parent_id = 57 ) AS secondLevelIds) union all SELECT p.id AS partyId, p.parent_id AS parentId, pul.username AS userName, p.city, p.birth_date AS birthDate, p.phone AS telePhoneNo FROM party p LEFT JOIN party_user_login pul ON pul.party_id = p.id WHERE 1=1 AND p.parent_id in ( select partyId FROM (SELECT p.id AS partyId, p.parent_id AS parentId, pul.username AS userName, p.city, p.birth_date AS birthDate, p.phone AS telePhoneNo FROM party p LEFT JOIN party_user_login pul ON pul.party_id = p.id WHERE 1=1 AND p.parent_id in ( SELECT partyId FROM (SELECT p.id AS partyId, p.parent_id AS parentId, pul.username AS userName, p.city, p.birth_date AS birthDate, p.phone AS telePhoneNo FROM party p LEFT JOIN party_user_login pul ON pul.party_id = p.id WHERE 1 = 1 AND p.parent_id = 57 ) AS secondLevelIds) )AS thirdLevelIds)