代码之家  ›  专栏  ›  技术社区  ›  Mark

mysql-返回按列分组的结果

  •  2
  • Mark  · 技术社区  · 16 年前

    我正在研究搜索/标记系统。我最初写的查询是为了在我的文章/视频表中存储“标题”、“描述”和逗号分隔的“标记”列。从那以后,我意识到了使我的标签正常化的好处。我现在有三张桌子要处理…

    冠词

    • 第三十二条
    • 标题
    • 描述
    • 内容

    TBLL塔格索引

    • 标记ID(代理主ID)
    • 标签类型(对于tbl_物品,等于1, 2个用于tbl_视频)
    • 标签字ID(见下表)
    • 标签\目标\ ID(文章\ ID/视频\ ID- 取决于标签类型)

    TB字词

    • 塔格线虫
    • 标签字(最后是实际标签)

    此查询返回标记…唯一的问题是它将它们作为不同的行返回。我想我需要将结果分组到同一行,这样我的搜索查询就可以工作了

    SELECT * 
      FROM `tbl_articles` A 
      JOIN `tag_index` I ON A.article_id = I.tag_target_id 
      JOIN tag_word W ON I.tag_word_id = W.tag_word_id 
     WHERE I.tag_type_id = 1
    

    这是我以前的搜索查询

    SELECT *, 
    (
    (CASE WHEN `description` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
    (CASE WHEN `description` LIKE '%london%' THEN 1 ELSE 0 END) + 
    (CASE WHEN `description` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
    (CASE WHEN `description` LIKE '%dog%' THEN 1 ELSE 0 END) +
    
    (CASE WHEN `title` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
    (CASE WHEN `title` LIKE '%london%' THEN 1 ELSE 0 END) + 
    (CASE WHEN `title` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
    (CASE WHEN `title` LIKE '%dog%' THEN 1 ELSE 0 END) +
    
    (CASE WHEN `tags` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
    (CASE WHEN `tags` LIKE '%london%' THEN 1 ELSE 0 END) + 
    (CASE WHEN `tags` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
    (CASE WHEN `tags` LIKE '%dog%' THEN 1 ELSE 0 END)
    
    ) AS relevance
    FROM `tbl_Articles`
    WHERE `description` LIKE '%hotel%'
      OR `description` LIKE '%london%'
      OR `description` LIKE '%lazy%'
      OR `description` LIKE '%dog%' 
      OR `title` LIKE '%hotel%'
      OR `title` LIKE '%london%'
      OR `title` LIKE '%lazy%'
      OR `title` LIKE '%dog%'
      OR `tags` LIKE '%hotel%'
      OR `tags` LIKE '%london%'
      OR `tags` LIKE '%lazy%'
      OR `tags` LIKE '%dog%'
    ORDER BY relevance DESC
    LIMIT 0 , 10;
    
    1 回复  |  直到 16 年前
        1
  •  3
  •   OMG Ponies    16 年前

    用途:

    SELECT A.*, 
           GROUP_CONCAT(DISTINCT w.tag_word ORDER BY w.tag_word ASC SEPARATOR ',') AS tags,
           COUNT(DISTINCT w.tag_word) +
           (CASE WHEN `description` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
           (CASE WHEN `description` LIKE '%london%' THEN 1 ELSE 0 END) + 
           (CASE WHEN `description` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
           (CASE WHEN `description` LIKE '%dog%' THEN 1 ELSE 0 END) +
    
           (CASE WHEN `title` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
           (CASE WHEN `title` LIKE '%london%' THEN 1 ELSE 0 END) + 
           (CASE WHEN `title` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
           (CASE WHEN `title` LIKE '%dog%' THEN 1 ELSE 0 END) AS relevance
      FROM tbl_articles A 
      JOIN tag_index I ON A.article_id = I.tag_target_id 
      JOIN tag_word W ON I.tag_word_id = W.tag_word_id 
     WHERE I.tag_type_id = 1
       AND w.tag_word IN ('hotel', 'london', 'lazy', 'dog')
    GROUP BY a.article_id, a.title, a.description, a.content
    ORDER BY relevance DESC
    
    推荐文章