代码之家 › 专栏 › 技术社区 › Anirudh Ramanathan

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

sql mysql

-1

Anirudh Ramanathan · 技术社区 · 12 年前

表格:

  +--------+-------------+-------------+
  | 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 子句和查询的大小是一个问题,因为我要处理更多的字段。我试图得到一个简洁的查询。有人能帮我吗?

2 回复 | 直到 12 年前

echo_Me 12 年前

我可能会给出这个解决方案,它会给你所需的结果,易于理解,也易于管理,以便稍后添加一些字段,甚至更快!。

  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

DEMO HERE

Anirudh Ramanathan 12 年前

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

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前