GROUP_CONCAT 方法 in mysql

MySQL’s aggregate function GROUP_CONCAT() which allows you to take multiple values in a group and produce a comma-separated list:

  • SELECT  l.full_name AS name,  l.url AS url ,  l.description AS body,  l.location_photo AS picture,  l.signup_date AS published_at,  l.logo AS logo,  l.directions AS tags,  GROUP_CONCAT(c.label) AS categories,  l.directions AS menu,  lcp.text AS coupon,  d.destination_name AS destinations,  l.street_address AS address1,  l.directions AS address2,  l.city AS city ,  l.state AS state ,  l.zip AS zip ,  l.directions AS events , l.hours AS hours, l.phone AS phone,  l.full_name AS seo_title,  l.keywords AS seo_description,  l.keywords AS seo_keywords
    FROM  listing l LEFT JOIN listing_coupon lcp ON lcp.listing_id=l.id, destination d, listing_category lc, category c
    WHERE l.destination=d.id AND l.id=lc.listing_id AND lc.category_id=c.id
    GROUP BY l.id
当一个listing有multiple categories的时候,categories将group到field,comma分开
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s