>

광고 테이블과 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


  • 답변 # 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
    
    

  • 이전 java - 유형 A와 B 사이의 주기적 종속성
  • 다음 exec - tcl - 동일한 파일에서 새 프로세스로 proc 실행