user_info要关联查出其它社交表里的信息，但是其它社交表可能没有这个用户
select u.*, COALESCE(u.slogan, tw.description, i.bio, g.bio,tu.description) as bio from user_info u LEFT OUTER JOIN twitter_user tw ON u.user_name = tw.screen_name LEFT OUTER JOIN instagram_user i ON u.user_name = i.username LEFT OUTER JOIN github_user g ON u.user_name = g.login LEFT OUTER JOIN tumblr_user tu ON u.user_name = g.name
引出了另外一个问题：postgresql应如何判断空字符串
postgresql多表join 中用了 COALESCE
但是空的string还是会被选出来''
得再加个NULLIF判断来解决
select u.*, COALESCE(NULLIF(u.slogan,''), NULLIF(tw.description,''), NULLIF(i.bio,''), NULLIF(g.bio,''), NULLIF(tu.description,'')) as bio from user_info u LEFT OUTER JOIN twitter_user tw ON u.user_name = tw.screen_name LEFT OUTER JOIN instagram_user i ON u.user_name = i.username LEFT OUTER JOIN github_user g ON u.user_name = g.login LEFT OUTER JOIN tumblr_user tu ON u.user_name = g.name
于是改成了这样
select u.*, COALESCE(NULLIF(u.slogan,''), NULLIF(tw.description,''), NULLIF(i.bio,''), NULLIF(g.bio,''), NULLIF(tu.description,'')) as bio from user_info u LEFT OUTER JOIN twitter_user tw ON u.user_name = tw.screen_name LEFT OUTER JOIN instagram_user i ON u.user_name = i.username LEFT OUTER JOIN github_user g ON u.user_name = g.login LEFT OUTER JOIN tumblr_user tu ON u.user_name = g.name
postgresql多表join方法的用法_postgresql多表join方法的用法
http://m.bbyears.com/shujuku/84821.html
推荐访问: