sql - 在 SQL 查询中从一个表中删除重复项并合并数据

Question

我想创建下表(表 1):

CHANNEL    START    STOP    SIGNAL    NOISE
-------------------------------------------
chan1      0        1       A         B
chan2      10       20      C         D
...

来自下表(表2):

CHANNEL    START    STOP    TYPE      VALUE
-------------------------------------------
chan1      0        1       signal    A
chan1      0        1       noise     B
chan2      10       20      signal    C
chan2      10       20      noise     D
...

我正在尝试创建一个选择查询以从表 2 中获取相关信息并从中创建表 1。然后我会把它变成一个 View 。

此时我基本上迷路了。我尝试执行以下操作:

select distinct 
    table2.CHANNEL as CHANNEL_ID
    , table2.START as START_FREQ
    , table2.STOP_FREQ as STOP_FREQ
    , SIGNAL = CASE
      WHEN table2.TYPE = 'signal' THEN table2.VALUE
      END
    , NOISE_OFFSET = CASE
      WHEN table2.TYPE = 'noise' THEN table2.VALUE
      END

from 
    table2

这给出了有趣的结果，但显然不是我想要的。我知道这是错误的查询，我可能应该使用连接来执行此操作，但是当我走那条路线时，查询将不会执行。我在这里放屁，非常感谢帮助。谢谢!

Answer 1

您正在寻找这个:

SELECT
      A.CHANNEL AS CHANNEL_ID,
      A.START AS START,
      A.STOP AS STOP,
      A.VALUE AS SIGNAL,
      B.VALUE AS NOISE
FROM
      table2 as A
      JOIN table2 as B
      ON 
          A.CHANNEL = B.CHANNEL AND 
          A.START = B.START AND 
          A.STOP = B.STOP AND
          A.TYPE = 'signal' AND
          B.TYPE = 'noise'