홈>2020-02-25 23:10

php - 필드가있는 SQL 조인 광고는 필드를 열로 표시합니다

광고 테이블과 Ads_field 테이블이 있고 가입 할 때 하나의 광고에 여러 개의 필드가 있으므로 여러 행이 표시됩니다

필드를 열로 표시하는 방법이 있습니까?

SELECT Ads.CITY_ID,Ads.PRICE,Ads.CREATED_DATE,Ads.HAS_IMAGES, 
 Ads.TITLE, Ads.DESCRIPTION, loc_cities.name as city, CATEGORY.NAME as           
 category_name, loc_countries.country_name as 
 country,Ads_fields.field_value
 FROM Ads
 INNER JOIN loc_cities ON Ads.CITY_ID=loc_cities.id
 INNER JOIN CATEGORY ON Ads.CATEGORY_ID=CATEGORY.ID 
 INNER JOIN loc_countries ON Ads.country_code=loc_countries.ID 
 INNER JOIN Ads_fields ON Ads.ID=Ads_fields.ad_id
    WHERE CONVERT(Ads.TITLE USING utf8) LIKE '%reqqa%' 
 or CONVERT(Ads.DESCRIPTION USING utf8) LIKE '%reqqa%'
 or CONVERT(loc_cities.name USING utf8) LIKE '%reqqa%'
 or CONVERT(loc_countries.country_name USING utf8) LIKE '%reqqa%'
 or CONVERT(CATEGORY.name USING utf8) LIKE '%reqqa%'
 LIMIT 50

phpmysqlsqljoin

답변 # 1

간단한 방법은 field_value를 문자열 쉼표로 구분하여 표시하는 것입니다

 SELECT 
      Ads.CITY_ID,Ads.PRICE
      , Ads.CREATED_DATE,Ads.HAS_IMAGES
      , Ads.TITLE
      , Ads.DESCRIPTION
      , loc_cities.name as city
      , CATEGORY.NAME as  category_name
      , loc_countries.country_name as country 
      , group_concat(Ads_fields.field_value)
   FROM Ads
   INNER JOIN loc_cities ON Ads.CITY_ID=loc_cities.id
   INNER JOIN CATEGORY ON Ads.CATEGORY_ID=CATEGORY.ID 
   INNER JOIN loc_countries ON Ads.country_code=loc_countries.ID 
   INNER JOIN Ads_fields ON Ads.ID=Ads_fields.ad_id
      WHERE CONVERT(Ads.TITLE USING utf8) LIKE '%reqqa%' 
   or CONVERT(Ads.DESCRIPTION USING utf8) LIKE '%reqqa%'
   or CONVERT(loc_cities.name USING utf8) LIKE '%reqqa%'
   or CONVERT(loc_countries.country_name USING utf8) LIKE '%reqqa%'
   or CONVERT(CATEGORY.name USING utf8) LIKE '%reqqa%'
   GROUP BY  Ads.CITY_ID,Ads.PRICE
      , Ads.CREATED_DATE,Ads.HAS_IMAGES
      , Ads.TITLE
      , Ads.DESCRIPTION
      , loc_cities.name 
      , CATEGORY.NAME 
      , loc_countries.country_name 
   LIMIT 50

Python2.net

php - 필드가있는 SQL 조인 광고는 필드를 열로 표시합니다

관련 자료