在SQL字段中有一个数组。 如何系统地显示它？

Question

I have a field in a data feed coming in with some values separated by commas. For one record, the values are:

A06,C05,C06,C15,C18,C19,C21,C22,E05,E22,G11,J02,J07,L04,L07,M01,M05,N03,N07,N10,N11,N12,N18,N19,N20,N24,O02,O03,O04,O06,O09,O14,O15,O16,O20,O21,O31,Q01,Q04,Q08,R07,S08,T08,T12,T23,T32,U01,U03,U04,U06,U13,W09,W11,W16,W19,W30,W45,X02,X03,X12,Z07

I have a separate table with some descriptions as to what each code means. When I query the main table and get this field name as a value, I can use explode to get it into an array and use a foreach loop to output each value.

The problem is, I want to display the description stored in another table. What's the proper way of iterating through this to display these values in a list?

As an example, C21 means "Gated Community."

Answer 1

You can use FIND_IN_SET() function for that.

Example you have record like this

Orders Table
------------------------------------
OrderID     |     attachedCompanyIDs
------------------------------------
   1                     1,2,3               -- comma separated values
   2                     2,4     

and

Company Table
--------------------------------------
CompanyID      |        name
--------------------------------------
    1                 Company 1
    2                 Another Company
    3                 StackOverflow
    4                 Nothing

Using the function

SELECT name 
FROM orders, company
WHERE orderID = 1 AND FIND_IN_SET(companyID, attachedCompanyIDs)

will result

name
---------------
Company 1
Another Company
StackOverflow