五张表如下:
cdn_duty表(a)
cdn_duty2city表(b)
cdn_duty2province表(c)
city_data表(d)
province_data表(e)
下面是我写的一部分查询:
问题是我不明白如何将c表关联进去实现这个效果:
小白求大神指教!!多谢!!!
五张表如下:
cdn_duty表(a)
cdn_duty2city表(b)
cdn_duty2province表(c)
city_data表(d)
province_data表(e)
下面是我写的一部分查询:
问题是我不明白如何将c表关联进去实现这个效果:
小白求大神指教!!多谢!!!
琢磨了三天终于实现了下面是完整查询语句,(可能不是最好的,欢迎大神指教!)
SELECT
aa.sno,
aa.duty_name,
aa.duty_mail,
aa.city_name,
concat(
IFNULL(aa.province_name, ''),
IFNULL(bb.province_name, '')
) AS province_name
FROM
(
SELECT
a.sno,
a.duty_name,
a.duty_mail,
d.city_name,
e.province_name
FROM
qiyq.cdn_duty AS a
LEFT JOIN qiyq.cdn_duty2city AS b ON a.sno = b.sno_duty
LEFT JOIN qiyq2.city_data AS d ON b.sno_city = d.sno
LEFT JOIN qiyq2.province_data AS e ON d.sno_province = e.sno
) aa
LEFT JOIN (
SELECT
a.sno,
a.duty_name,
a.duty_mail,
e.province_name
FROM
qiyq.cdn_duty AS a
LEFT JOIN qiyq.cdn_duty2province AS c ON a.sno = c.sno_duty
LEFT JOIN qiyq2.province_data AS e ON c.sno_province = e.sno
) bb ON aa.sno = bb.sno
ORDER BY
aa.sno
其中concat参考自:https://zhidao.baidu.com/question/547611101.html
效果如下: