duanci8209
duanci8209
2015-05-12 18:02

SQL列表最受欢迎的项目[关闭]

已采纳

I have a table named kb_views and each time an item is viewed it inserts a new row into this table

there is a column named kb_seq which is the sequence number of the item in a table called kb

whats the best way using PHP to select to most popular 10 items?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • douwen1313 douwen1313 6年前

    the best way will be the sql query itself, if kb_seq includes the count of each element views you can pull the top 10 by using

    order by `kb_seq`.`count` desc limit 10
    

    and your query should look like this:

    select * from `kb_seq` order by `kb_seq`.`count` desc limit 10
    

    if you dont have such table and only one table wich each row is a view you will have to use group

    select count(*) as `viewed_times`, id FROM `kb_views` group by `viewed_item_id` order by `viewed_times` desc  limit 10 
    
    点赞 评论 复制链接分享
  • duanbenzan4050 duanbenzan4050 6年前

    Does this fit your requirement:

    SELECT 
        d.* 
    FROM 
        kb d,
        (SELECT 
            s.kb_seq, COUNT(*)
        FROM 
             kb s
        INNER JOIN 
            kb_views ct ON ct.kb_seq = s.kb_seq
        GROUP BY 
            s.kb_seq 
        Order by 
            2 DESC LIMIT 10) f
    where 
        d.kb_seq = f.kb_seq
    

    See results here sqlfiddle

    点赞 评论 复制链接分享

为你推荐