在单个表上使用UNION ALL折叠列的简明语法

Question

表格：

  +--------+-------------+-------------+
  | TIME   |   TYPE_X    |   TYPE_Y    |
  |--------|-------------|-------------|
  |  T1    |   VAL_X_1   |   VAL_Y_1   |
  |  T2    |   VAL_X_2   |   VAL_Y_2   |
  +--------v-------------v-------------+

期望输出：

  +--------+-------------+-------------+
  | TIME   |   TYPE      |     VAL     |
  |--------|-------------|-------------|
  |   T1   |   TYPE_X    |   VAL_X_1   |
  |   T2   |   TYPE_X    |   VAL_X_2   |
  |   T1   |   TYPE_Y    |   VAL_Y_1   |
  |   T2   |   TYPE_Y    |   VAL_Y_2   |
  +--------v-------------v-------------+

我想到的是使用UNION ALL(有效(

(SELECT 
  `TIME`,
  'TYPE_X' AS TYPE,
  `TYPE_X` AS VAL
  FROM
  T WHERE ...)
  UNION ALL
  (SELECT `TIME`,
  'TYPE_Y' AS TYPE,
  `TYPE_Y` AS VAL
  FROM
  T WHERE ...)

但是，当我处理更多字段时，重复的WHERE子句和查询的大小是一个问题。我正试图找到一个简洁的问题。有人能帮我吗？

Answer 1

我可能会给出这个解决方案，它将为您提供所需的结果，易于理解，也易于管理，以便稍后添加一些字段，甚至更快！。

  select time, type , val 
  from(
    select 
      TIME,
      CASE when TYPE_X = 'VAL_X_1' OR TYPE_X = 'VAL_X_2' then 'TYPE_X'  end as TYPE,
      CASE when TYPE_X = 'VAL_X_1' OR TYPE_X = 'VAL_X_2' then TYPE_X end as Val
    from Table1 
  UNION
   select 
     TIME,
     CASE when TYPE_Y = 'VAL_Y_1' OR TYPE_Y = 'VAL_Y_2' then 'TYPE_Y' end as TYPE,
     CASE when TYPE_Y = 'VAL_Y_1' OR TYPE_Y = 'VAL_Y_2' then TYPE_Y end as Val
    from Table1
  )t

演示此处

Answer 2

select 
  TIME,
  if (temp.row_n=1,t.TYPE_X,t.TYPE_Y) as TYPE,
  if (temp.row_n=1,t.TYPE_X,t.TYPE_Y) as VAL
from t,
     (select 1 as row_n
      union
      select 2 as row_n) temp