Question

我在数据库里有一个表格, 我储存新闻文章的分类, 用户每次读文章时, 都会在相关栏目中加增值。例如 :

""https://i.sstatic.net/H4TZ.png" alt="此处输入图像描述"/ >

现在我要执行一个查询, 在那里我可以得到每个记录最高值为 4 的列名。例如用户 9, 它会返回这个 :

""https://i.sstatic.net/yehnN.png" alt="此处的内置图像描述"/"

我尝试了好几次搜索了很多但不知道怎么做有人能帮我吗?

Answer 1

为此,应:

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" rel="noreferrer" >http://www.sqlfiddle.com/#!2/ff624/11

Answer 2

以下展示了这样做的一个非常简单的方法。

select userId, substring_index(four_highest, , ,1) as  highest value , substring_index(substring_index(four_highest, , ,2), , ,-1) as  2th highest value ,  substring_index(substring_index(four_highest, , ,3), , ,-1) as  3 rd highest value ,  substring_index(four_highest, , ,-1) as  4th highest value    from
(
select userid, convert(group_concat(val) using utf8) as four_highest from
(
select userId,Buitenland as val, Buitenland  as col from test where userid=9 union
select userId,Economie as val,  Economie  as col from test where   userid=9 union
select userId,Sport as val , Sport  as col from test where  userid=9 union
select userId,Cultuur as val, Cultuur  as col from test where userid=9 union
select userId,Wetenschap as val, Wetenschap  as col from test where userid=9 union
select userId,Media as val, Media  as col from test where  userid=9 order by val desc limit 4
) inner_query
)outer_query;

Answer 3

PL/SQL, 可能吗? 设置用户_ id, 查询您的表格, 将返回的行存储为 nx2 列名称和值的 nx2 数组( 列数是 n), 并根据值排序数组。

当然,正确的做法是按照@octern的建议重新设计数据库。

Answer 4

这将让您开始从单行的多个列中获取最高值的概念( 修改您的具体表格 - 我创建了一个假表格 ) 。

create table fake 
(
  id int Primary Key,
  col1 int,
  col2 int,
  col3 int,
  col4 int 
)
insert into fake values (1, 5, 9, 27, 10)
insert into fake values (2, 3, 5, 1, 20)
insert into fake values (3, 89, 9, 27, 6)
insert into fake values (4, 17, 40, 1, 20)

SELECT *,(SELECT Max(v) 
FROM (VALUES (col1), (col2), (col3), (col4) ) AS value(v))
FROM fake

友情链接