mySql-使用逗号分隔值列表创建联接 [英] mySql - creating a join using a list of comma separated values

问题描述

我有一张桌子，上面有一个商户名称字段，以及他们提供的服务字段.服务字段是用逗号分隔的整数列表，该整数与另一个服务表相关，带有服务ID和服务名称字段.

I've got a table with a field for Merchant's name and field with the Services they provide. The Services field is a comma separated list of integers that relate to another Services table, with the Service id and the Service Name fields.

我正在尝试创建一个将两者结合在一起的查询，因此我可以拥有一个商人列表以及服务名称.到目前为止，我的解决方案是在我的初始"foreach"循环中进行第二个循环，但这可能意味着每个商家名称需要进行5或6个db调用.

I'm trying to create a single query that joins those two, so I can have a list of Merchants, along with the Services Names. My solution so far has been to do a second loop within my initial 'foreach' loop, but that can mean 5 or 6 db calls for each Merchant name.

经过StackOverflow-ing(google-ing)之后，我注意到使用逗号分隔的字段可能不是最好的方法.

After some StackOverflow-ing (google-ing), I noticed that using a comma separated field is probably not the best way to go.

任何人要么有进行连接的方法，要么有关于如何更好地设置数据库结构的想法?提前非常感谢！

Anyone have either a way to do the join, or thoughts on how the db structure could be set up better? Many thanks in advance!

推荐答案

Merchant
MerchantId   Name
          1   Adams Consulting

Merchant_Services
MerchantId    Service
         1    SEO
         1    Brand Consulting

您实际上可以返回以逗号分隔的列表:

You can actually get a comma separated list back:

SELECT m.*, GROUP_CONCAT(ms.Service) AS Services
FROM Merchant m
LEFT JOIN Merchant_Serivces ms
ON ms.MerchantId = m.MerchantId
GROUP BY m.MerchantId
ORDER BY m.Name, ms.Service

结果:

MerchantID  Name              Services
----------  ----------------  --------------------
         1  Adams Consulting  Brand Consulting,SEO

这篇关于mySql-使用逗号分隔值列表创建联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！