时间戳

首页 > 解决方案 > MYSQl 按计数最佳标签匹配查询顺序

php - MYSQl 按计数最佳标签匹配查询顺序

问题描述

我正在尝试对 MYSQL 查询进行排序，并希望显示具有最佳标签匹配的结果，首先显示。尝试了 6 个多小时。为什么 ORDER BY 不起作用？

当我手动为标签“293,2376,231,8,2088,9,171,4,18,7,1,40,657,23,2339,5,96,2904,702,1936”添加报价时，它确实是第一位的。 . 但是第二名的引用只有 4 个标签匹配，第 10 名得到 7 个匹配。

数据库

物品标签

itemid, 
tagid

引号

id, 
bg_image, 
author, 
active

报价单

id, 
quote

<?php
$topictagid = "293,2376,231,8,2088,9,171,4,18,7,1,40,657,23,2339,5,96,2904,702,1936";

$sQuery = "SELECT quotes.id, bg_image, author, tagid, active, itemid, quote     
            FROM itemtag
                LEFT JOIN quotes ON quotes.id = itemtag.itemid
                LEFT JOIN quotes_en ON itemtag.itemid = quotes_en.id
            WHERE tagid IN (".$topictagid.") 
            AND quotes.active = 1 
            GROUP BY itemid 
            having count(tagid) > 2 
            ORDER BY count(tagid) DESC
            LIMIT 50";
?>

标签： phpmysql

解决方案

您的ORDER BY是否适用于此？

$topictagid = "5,10,16,17";

$sQuery = "SELECT quotes.id, bg_image, author, tagid, active, itemid, quote, count(tagid) as countag     
            FROM itemtag
                LEFT JOIN quotes ON quotes.id = itemtag.itemid
                LEFT JOIN quotes_".$language." ON itemtag.itemid = quotes_".$language.".id
            WHERE tagid IN (".$topictagid.") 
            AND quotes.active = 1 
            GROUP BY itemid 
            having count(tagid) > 2 
            ORDER BY countag DESC ".$sortby." 
            LIMIT 50";

推荐阅读