mysql查询显示最喜欢的帖子

inb24sb2 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(344)

我想从数据中数一数一周内最受欢迎的帖子。但是我的朋友很困惑我怎么能数出来。我也只想展示一周来第一个最受欢迎的帖子。我搜索了一个示例答案，但没有找到任何结果。你能帮帮我吗。这个表是这样的，时间是unixtimestamp

+------------+------------+------------+------------+
|  post_id   | date       |  user_id   | post_id_fk |
+------------+-------------------------+------------+
|     32     | 1535624966 |   21       |   148      |
|     33     | 1535624990 |   68       |   148      |
|     34     | 1535625007 |   99       |   758      |
|     35     | 1535625022 |   12       |   148      |
|     36     | 1535625039 |   43       |   148      |
+------------+---------------------+----------------+

查询应为count post_id_fk 在一周内使用日期unixtimestamp和打印最喜欢的 post_id_fk .
我试过像下面的查询，但它只是数行，但我需要最流行的 post_id_fk 而且它应该在一周内展出。

$query = mysqli_query($this->db,"SELECT 
COUNT(*) AS postLikeCount FROM post_like 
WHERE post_id_fk = post_id_fk") 
or die(mysqli_error($this->db));    
$row = mysqli_fetch_array($query, MYSQLI_ASSOC);  
return $row['postLikeCount'];

mysql php

来源：https://stackoverflow.com/questions/52095217/mysql-query-show-most-liked-post

3条答案

按热度按时间

hwamh0ep1#

您必须按post\u id对结果进行分组 SELECT COUNT(*) AS count FROM post_like WHERE /* here condition for week */ GROUP BY post_id SORT BY count DESC

赞(0）回复(0）举报 2021-06-21

lhcgjxsq2#

在MySQL8+之前的版本中，这是一个棘手的查询，因为需要聚合两次；一次找出每个帖子和每周的计数，然后再次找出这些组中最大的计数。更让人困惑的是，我们还必须将unix时间戳转换为一种可以轻松使用的日期格式。

SELECT
    t1.yearweek,
    t1.post_id_fk,
    t1.cnt AS max_count
FROM
(
    SELECT
        YEARWEEK(FROM_UNIXTIME(date)) AS yearweek,
        post_id_fk,
        COUNT(*) AS cnt
    FROM post_like
    GROUP BY
        YEARWEEK(FROM_UNIXTIME(date)),
        post_id_fk

) t1
INNER JOIN
(
    SELECT yearweek, MAX(cnt) AS max_cnt
    FROM
    (
        SELECT
            YEARWEEK(FROM_UNIXTIME(date)) AS yearweek,
            post_id_fk,
            COUNT(*) AS cnt
        FROM post_like
        GROUP BY
            YEARWEEK(FROM_UNIXTIME(date)),
            post_id_fk
    ) t
    GROUP BY yearweek
) t2
    ON t1.yearweek = t2.yearweek AND t1.cnt = t2.max_cnt;

演示

赞(0）回复(0）举报 2021-06-21

eqfvzcg83#

我知道这个问题已经有了答案，但这个问题不是你想要的吗？

SELECT COUNT(*) AS postLikeCount, date, post_id_fk 
FROM post_like
WHERE
    date BETWEEN FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 day))) 
    AND FROM_UNIXTIME(UNIX_TIMESTAMP) 
group by post_id_fk
order by postLikeCount DESC

赞(0）回复(0）举报 2021-06-20

我来回答

mysql查询显示最喜欢的帖子

3条答案

演示

相关问题

热门标签

最新问答