获取具有行sql中的最大值的列名

I have a a table in my database where I store categories for newsarticles and each time a user reads an article it increments the value in the associated column. Like this:

我的数据库中有一个表，用于存储新闻文章的类别，每次用户阅读一篇文章时，它都会增加相关列中的值。是这样的:

Now I want to execute a query where I can get the column names with the 4 highest values for each record. For example for user 9, it would return this:

现在，我想执行一个查询，在那里我可以得到每个记录的4个最高值的列名。例如，对于用户9，它将返回:

I've tried several things, searched a lot but don't know how to do it. Can anyone help me?

我尝试过很多东西，搜索过很多，但是不知道怎么做。谁能帮我吗?

4 个解决方案

#1

This should do it:

这应该这样做:

select
  userid,
  max(case when rank=1 then name end) as `highest value`,
  max(case when rank=2 then name end) as `2nd highest value`,
  max(case when rank=3 then name end) as `3rd highest value`,
  max(case when rank=4 then name end) as `4th highest value`
from
(
  select userID, @rownum := @rownum + 1 AS rank, name, amt from (
    select userID, Buitenland as amt, 'Buitenland' as name from newsarticles where userID = 9 union
    select userID, Economie, 'Economie' from newsarticles where userID = 9 union
    select userID, Sport, 'Sport' from newsarticles where userID = 9 union
    select userID, Cultuur, 'Cultuur' from newsarticles where userID = 9 union
    select userID, Wetenschap, 'Wetenschap' from newsarticles where userID = 9 union
    select userID, Media, 'Media' from newsarticles where userID = 9
  ) amounts, (SELECT @rownum := 0) r
  order by amt desc
  limit 4
) top4
group by userid

Demo: http://www.sqlfiddle.com/#!2/ff624/11

演示:http://www.sqlfiddle.com/ ! 2 / ff624/11

4 个解决方案

#1

更多相关文章

随机推荐