选择所有大于某个值的列 [英] Select all columns greater than some value

问题描述

我是mysql的新手，我具有这样的结构

I am new to mysql and I have structure like this

timestamp | Name | Total | Student1 | Student2 | Student3
20150911  | X    | 100   |       76 |       10 |       NA
20151111  | Y    | 70    |       NA |        0 |       23

现在我要使用mysql查询选择给定的'时间戳'在哪里

Now I want to select with mysql query where at given 'timestamp'

• 所有非零学生列
• 所有不适用的学生专栏
• 所有学生列为零

预期结果

• 如果我输入非零"查询，则应将列名称命名为时间戳20150911的"Student1，Student2"
• 如果我输入零"查询，则应将时间戳20150911的列名称指定为().
• 如果我输入"NA"查询，则应为时间戳20150911将该列的名称指定为"Student3"

我已经尝试搜索这种混合选择，但是到目前为止，我还没有成功.大多数答案都集中在行选择而不是列上.

I have tried searching for such mixed selection but so far I am not successful. Most of answers are concentrating on row selection rather columns.

推荐答案

您的数据结构未规范化.但是，如果您想走这条路，请使用:

Your data structure is not normalized. But if you want to go this path use:

select sub.student
FROM (
  select t.timestamp,
    t.Name,
    t.Total,
    c.col AS student,
    case c.col
      when 'Student1' then Student1
      when 'Student2' then Student2
      when 'Student3' then Student3
      -- ...
    end as d
  from mytable t
  cross join
  (
    select 'Student1' as col
    union all select 'Student2'
    union all select 'Student3'
    -- ...
  ) c
) AS sub
WHERE sub.timestamp = '20150911'
  AND sub.d > 0;
  -- sub.d = 'NA'
  -- sub.d = 0

SqlFiddleDemo

输出:

╔══════════╗
║ student  ║
╠══════════╣
║ Student1 ║
║ Student2 ║
╚══════════╝

如果要用逗号分隔结果，请使用:

If you want comma separated result use:

select GROUP_CONCAT(sub.student ORDER BY sub.student) AS result

SqlFiddleDemo2

输出:

╔═══════════════════╗
║       result      ║
╠═══════════════════╣
║ Student1,Student2 ║
╚═══════════════════╝

这篇关于选择所有大于某个值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！