如何使用 php 在 mysql 中计算匹配的标签百分比 - How matched tags percentage can be calculated in mysql with php

How matched tags percentage can be calculated in mysql with php

>我有3张桌子，第一个是播放列表，另外两个是关键字和关键字映射。

我使用下面的sql来查找共享公共标签的播放列表。我需要在 mysql+php 中获取通用标签百分比并按它排序即使我可以获得匹配的标签计数，我也可以在以后以 php 计算百分比。

表格是;按顺序播放列表，关键字，关键字映射

|pid  |authorid |totaltrack|
|kid  |keyword  |used       |createdate|
|kmid |kwid     |plid       |

以前的代码;

SELECT p1.pid FROM keywordmap km
LEFT JOIN playlists p1 ON km.plid = p1.pid
WHERE kwid 
IN (
    SELECT kwid FROM playlists p2 LEFT JOIN keywordmap km2 
    ON p2.pid = km2.plid LEFT JOIN keywords kws
    ON km2.kwid = kws.kid WHERE p2.pid = 10
)
GROUP BY plid

期望的结果是;

|plid |matchedtag|percentage
|234  |23        | 65 
|363  |21        | 55
|19   |18        | 34
|91   |1         | 3

所以这就是答案。其余百分比根据用户的总标签数计算。

SELECT p1.pid, COUNT(plid) as matched, p1.authorid FROM keywordmap km
LEFT JOIN playlists p1 ON km.plid = p1.pid
WHERE kwid 
IN (
    SELECT kwid FROM playlists p2 LEFT JOIN keywordmap km2 
    ON p2.pid = km2.plid LEFT JOIN keywords kws
    ON km2.kwid = kws.kid WHERE p2.pid = $pid
)
GROUP BY plid ORDER BY matched DESC