Mysql扩展查询

在左连接查询中根据已有条件查询另一个表中符合条件的数据。

案例一

       根据查询到的当前会员Id与他上级Id,查找他上级Id在会员表中对应的昵称。

SELECT 
	(SELECT wechatNickname FROM gs_member WHERE id=r.pid) as 'pidName',
	r.pid
FROM
	gs_member AS m
	LEFT JOIN gs_relation AS r ON r.childId = m.id 
WHERE
	m.delFlag = '0'

查询结果

扩展

       根据查询到的当前会员Id与他上级Id,查找他上级Id在会员表中对应的昵称，如果没有赋值为"无"。

SELECT
IF
	(
	( SELECT gs_member.wechatNickname FROM gs_member WHERE gs_member.id = r.pid ) = "" || ( SELECT gs_member.wechatNickname FROM gs_member WHERE gs_member.id = r.pid ) IS NULL,
	'无',
	( SELECT gs_member.wechatNickname FROM gs_member WHERE gs_member.id = r.pid ) 
	) AS 'pidName',
	r.pid 
FROM
	gs_member AS m
	LEFT JOIN gs_relation AS r ON r.childId = m.id 
WHERE
	m.delFlag = '0'

 查询结果