table a
-------------------------------------------------
id name wishlist1 wishlist2 wishlist3
1 john 1 2 3
2 paul 4 5
table b
--------------------------------------------------
id goods
1 car
2 aircraft
3 bicycle
4 motorbike
5 ipad
result i want to get
---------------------------------------------------
john car aircraft bicycle
paul motorbike ipad
我怎么能得到这个结果？
(在MysqL中)
最佳答案
这最多可输出3个愿望(如果没有愿望,则在愿望列中显示空值)
select
name,g1.goods as wish1,g2.goods as wish2,g3.goods as wish3
from tablea a
left join tableb g1.id on g1.wishlist1
left join tableb g2.id on g1.wishlist2
left join tableb g3.id on g1.wishlist3
这可能会更好,如果你不介意以逗号分隔的愿望清单,那就更好了：
select
name,group_concat(goods) as wishes
from tablea a
left join tableb b on b.id in (a.wishlist1,a.wishlist2,a.wishlist3)
group by name;
这将输出：
name | wishes
------|----------------------
john | car,aircraft,bicycle
paul | motorbike,ipad