单个表上的简洁语法以使用 UNION ALL 折叠列

问题描述

表格:

  +--------+-------------+-------------+
  | 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(有效)

What I came up with uses UNION ALL (which works)

(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 子句和查询的大小是一个问题，因为我要处理更多的字段.我正在尝试进行简洁的查询.有人可以帮我吗?

However, the repeated WHERE clauses and size of the query are a problem as I deal with more fields. I'm trying to get to a concise query.Could someone help me out here?

推荐答案

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

这篇关于单个表上的简洁语法以使用 UNION ALL 折叠列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！