下面region为区域表,其中parentid为该区域的上级区域id,怎样写一条sql可以获取每个城市的信息,格式如:中国 安微省 广州市。
SELECT r2.`name` 'country', r1.`name` 'province', r.`name` 'city'
FROM `region` r
INNER JOIN `region` r1 ON r.parentid = r1.id
INNER JOIN `region` r2 ON r1.parentid = r2.id
SELECT r2.`name` 'country', r1.`name` 'province', r.`name` 'city'
FROM region r
INNER JOIN (
INNER JOIN ( ) r2 ON r1 . parentid = r2 . id;
执行结果如下:
用LEFT JOIN如下:
SELECT r2.`name` '国家', r1.`name` '省级', r.`name` '市级'
FROM region r
LEFT JOIN (
LEFT JOIN ( ) r2 ON r1 . parentid = r2 . id
WHERE r . parentid <> ''
AND r1 . parentid <> '';
下面member为会员表,其中country、province及city都是关联的区域表中的id,怎样用一条sql取出用户的信息,格式如:张三 中国 安微省 合肥市
SELECT m.`name` AS 'userName', r2.name AS 'country', r1.name AS 'province', r.name AS 'city'
FROM `member` m
INNER JOIN `region` r ON m.`city` = r.id
INNER JOIN `region` r1 ON m.`province` = r1.id
INNER JOIN `region` r2 ON m.`country` = r2.id
FROM `member` m
INNER JOIN `region` r ON m.`city` = r.id
INNER JOIN `region` r1 ON m.`province` = r1.id
INNER JOIN `region` r2 ON m.`country` = r2.id
SELECT m.`name` AS 'userName', r2.name AS 'country', r1.name AS 'province', r.name AS 'city'
FROM `member` m
LEFT JOIN ( ) r ON m . `city` = r . id
LEFT JOIN ( ) r1 ON m . `province` = r1 . id
LEFT JOIN ( ) r2 ON m . `country` = r2 . id
FROM `member` m
LEFT JOIN ( ) r ON m . `city` = r . id
LEFT JOIN ( ) r1 ON m . `province` = r1 . id
LEFT JOIN ( ) r2 ON m . `country` = r2 . id
执行效果如下:(用INNER JOIN、LEFT JOIN均可以)