获取列名称，该列名称在行sql中具有最大值 [英] Get column name which has the max value in a row 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:

现在，我想执行一个查询，在该查询中我可以获取每条记录的4个最高值的列名.例如，对于用户9，它将返回以下内容:

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:

我尝试了几件事，进行了很多搜索，但不知道该怎么做.谁能帮我吗?

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

推荐答案

这应该做到:

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

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

这篇关于获取列名称，该列名称在行sql中具有最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！