首页 \ 问答 \ 如何使用php在mysql中计算匹配的标签百分比(How matched tags percentage can be calculated in mysql with php)

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

 我有3个表，第一个是播放列表，另外两个是关键字和keywordmap。  
 我使用下面的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

I have 3 tables, First one is playlists the other two are keywords and keywordmap. 
I use to use the sql below to find playlists that sharing common tags. I need to get common tag percent and order by it in mysql+php Even If I can get matched tag count, I can calculate percentage in php later. 
Tables are; in order playlists, keywords, keywordmap 
|pid  |authorid |totaltrack|

|kid  |keyword  |used       |createdate|

|kmid |kwid     |plid       |
 
previous code;  
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
 
Desired results are; 
|plid |matchedtag|percentage
|234  |23        | 65 
|363  |21        | 55
|19   |18        | 34
|91   |1         | 3

原文：https://stackoverflow.com/questions/29079343

更新时间：2023-08-07 20:08

最满意答案

 语法如下：  
update 
    contracts a, 
    contracts_history b 
set 
    a.col1=b.col1, 
    a.col2=b.col2 
where 
    a.id=b.id 
 
 尽管如此，在查询中没有指定ID，这意味着它将更新历史记录表中的所有行。 你可以像这样添加条件：  
update 
    contracts a, 
    contracts_history b 
set 
    a.col1=b.col1, 
    a.col2=b.col2 
where 
    a.id=b.id 
    and a.id=3

The syntax is as follows: 
update 
    contracts a, 
    contracts_history b 
set 
    a.col1=b.col1, 
    a.col2=b.col2 
where 
    a.id=b.id 
 
on that note though, you have no ID specified in the query, which means it will update ALL the rows that are in the history table. You can add condition like this: 
update 
    contracts a, 
    contracts_history b 
set 
    a.col1=b.col1, 
    a.col2=b.col2 
where 
    a.id=b.id 
    and a.id=3

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

最满意答案

相关问答

在另一个表中为每个ID插入表中的行(Insert row in table for each id in another table)[2022-09-02]

使用Jquery按顺序删除和更新动态创建的表行ID(Delete and Update dynamically created Table row ID's serially using Jquery)[2021-09-19]