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?

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 

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