本文已参与「新人创作礼」活动，一起开启掘金创作之路。

人员表：

id	name	age
1	赵	20
2	钱	21
3	孙	21
4	李	22
5	周	25

人员拥有汽车表：

id	car_name	person_id
0	car2-b	2
1	car1-a	1
2	car1-b	1
3	car2-a	2
4	car3-a	3

SELECT p.*,GROUP_CONCAT(c.car_name ) as cars

FROM person p 
LEFT JOIN car c
ON p.id = c.person_id

GROUP BY p.id

除此，还有一种写法

来自葛大神的神笔

查询结果集的子查询合并法：

SELECT
	(
		SELECT
			GROUP_CONCAT(
				DISTINCT keypropCode SEPARATOR ','
			)
		FROM
			household_user_key
		WHERE
			strUserGuid = k.strUserGuid
		AND isDelete = 0
		AND isEnable = 0
	) kcode,
	(
		SELECT
			GROUP_CONCAT(
				DISTINCT keypropName SEPARATOR ','
			)
		FROM
			household_user_key
		WHERE
			strUserGuid = k.strUserGuid
		AND isDelete = 0
		AND isEnable = 0
	) kname,
	(
		SELECT
			GROUP_CONCAT(
				DISTINCT keypropValue SEPARATOR ','
			)
		FROM
			household_user_key
		WHERE
			strUserGuid = k.strUserGuid
		AND isDelete = 0
		AND isEnable = 0
	) kvalue,
	bhu.*
FROM
	`household_user_key` k
LEFT JOIN building_household_user AS bhu ON bhu.strUserGuid = k.strUserGuid
AND k.isDelete = 0
GROUP BY
	k.strUserGuid

综合联查sql语句鉴赏

SELECT
	p.strOrganizationGuid,
	p.strOrganizationName,
	p.strOrganizationTypeName,
	parent.strOrganizationName AS parentName,
	p.strParentId,
	countT.count AS countSon,
	p.strSecretaryName,
	p.strContactsName,
	p.strContactsCellphone,
	bu.STR_LAT AS strLat,
	bu.STR_LNG AS strLng,
	sumM.count AS countMembers
FROM
	party_organization p
	
--解决自表联查count子栏目问题：parentID

LEFT JOIN ( 
	SELECT
		strParentId AS pid,
		count(*) count
	FROM
		party_organization
	GROUP BY
		pid
) countT ON p.strOrganizationGuid = countT.pid
--解决自表联查获取父层name问题：parentID
LEFT JOIN party_organization parent ON p.strParentId = parent.strOrganizationGuid
--解决外表联查获取拓展信息
LEFT JOIN base_unit bu ON bu.STR_CODE = p.strUnitCode
--解决两张外表 归属本表id的统计问题sum
LEFT JOIN (
	SELECT
		sum(count) AS count,
		unT.oid
	FROM
		(
			SELECT
				strPartyOrganizationId AS oid,
				count(*) count
			FROM
				base_user
			WHERE
				IS_DELETE = 0
			AND IS_ENABLE = 1
			AND isParty = 1
			AND strPartyOrganizationId IS NOT NULL
			AND strPartyOrganizationId != ''
			GROUP BY
				strPartyOrganizationId
			UNION ALL
				SELECT
					strPartyOrganizationId AS pid,
					count(*) count
				FROM
					household_user
				WHERE
					isDeleted = 0
				AND isEnable = 0
				AND isParty = 1
				AND strPartyOrganizationId IS NOT NULL
				AND strPartyOrganizationId != ''
				GROUP BY
					strPartyOrganizationId
		) unT
	GROUP BY
		oid
) sumM ON p.strOrganizationGuid = sumM.oid
WHERE
	p.isDelete = 0
AND p.isEnable = 1

        SELECT
        <foreach  item="item" collection="months" index="index" separator=","  >
            SUM(
            CASE
            WHEN #{item.start}  &lt;= strJoinPartyTime
            AND strJoinPartyTime  &lt;=  #{item.end} THEN
            1
            ELSE
            0
            END
            ) AS #{item.date}
            
        </foreach>

        FROM
        base_user
        WHERE
        IS_DELETE = 0
        AND IS_ENABLE = 1
        AND isParty = 1

单表带父层ID查询当前的直接子层

id	name	parentId

查询列表 查询每行的直接子层数

SELECT
	id,
	name,
	parentId,
	countT.count
FROM
	table1 p
LEFT JOIN (
	SELECT
		parentIdas pid,
		count(*) count
	FROM
		table1
	GROUP BY
		pid
) countT 

ON p.id = countT.pid