php - "join"函数上的 MySQL Math

Question

不太确定如何解释这一点，但是，基本上我有两个表，我需要对它们的值进行一些数学计算。除了加法(它会自动执行)之外，我遇到任何问题。

这是我正在使用的 MySQL 语句。

select snippet_id, count(snippet_id) as cnt
from snippets_likes join snippets_engagement on snippet_id = snippets_engagement.snip_id
group by snippet_id
order by cnt desc

此语句提取喜欢 + 参与的总数。

但是，我希望它等于点赞数 + ( engagements/1000 )。

建表

CREATE TABLE  `IOTunes`.`snippets_engagement` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `snip_id` int(10) unsigned NOT NULL DEFAULT '0',
  `artist_id` int(10) unsigned NOT NULL DEFAULT '0',
  `snip_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `engagement_type` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6694 DEFAULT CHARSET=latin1;

CREATE TABLE  `IOTunes`.`snippets_likes` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `snippet_id` int(10) unsigned NOT NULL DEFAULT '0',
  `artist_id` int(10) unsigned NOT NULL DEFAULT '0',
  `snippet_like` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=197 DEFAULT CHARSET=latin1;

我已经尝试了很多不同的公式，但是，无法让它发挥作用。有什么指点吗？

Answer 1

试试这个语句:

SELECT snippet_id, SUM(cnt) AS cnt
FROM (
   SELECT snippet_id, COUNT(1) AS cnt
   FROM snippets_likes 
   GROUP BY snippet_id
   UNION
   SELECT snippet_id, COUNT(1)/1000 AS cnt
   FROM snippets_engagement 
   GROUP BY snippet_id
) AS subQ
GROUP BY snippet_id
ORDER BY cnt DESC

或者如果你坚持加入...

select sl.snippet_id, count(DISTINCT sl.ID) + count(DISTINCT se.ID)/1000 as cnt
from snippets_likes AS sl join snippets_engagement AS se 
on sl.snippet_id = se.snip_id
group by snippet_id
order by cnt desc

不过我不推荐加入，您只会获得至少有一个“喜欢”和一个“参与”的“片段”的值。