sql - 如何从同一个表中查询不同的行

Question

RowNum  ,Source_element,    source_value

1   ,    header1      ,      mail
1   ,    header2     ,       location
1   ,    header3     ,       address

2   ,    header1       ,     mail
2   ,    header2    ,        location
2   ,    header3    ,        US

3   ,    header1       ,     Email
3   ,    header2       ,     location
3   ,    header3      ,      Canada

上面是我的桌子。目前作为示例，我为每个 Rownum 使用 3 条记录，但它可以是任何基于我们获得的源文件的记录

我需要在哪里写一个查询

如果任何给定的相同 rownum 的 header1=mail 和 header3=US 则提取该 rownum 2

或者

如果 header1=Email 和 header2=location 和 header3=Canada 然后提取 rownum 3

任何线索都会真正帮助我..我完全不知道如何进行。

标签： sqloracleoracle11g

Answer 1

不优雅，但是：

    CREATE TABLE Demo
    (
     Row_Num        VARCHAR2(0016)
    ,Source_Element VARCHAR2(0016)
    ,Source_Value   VARCHAR2(0016)
    );

INSERT INTO Demo (Row_Num,Source_Element,Source_Value)
SELECT * FROM
(
      SELECT '1' AS Row_Num, 'header1' AS Source_Element, 'mail' AS Source_Value FROM dual
UNION SELECT '1' AS Row_Num, 'header2' AS Source_Element, 'location' AS Source_Value FROM dual
UNION SELECT '1' AS Row_Num, 'header3' AS Source_Element, 'address' AS Source_Value FROM dual
--
UNION SELECT '2' AS Row_Num, 'header1' AS Source_Element, 'mail' AS Source_Value FROM dual
UNION SELECT '2' AS Row_Num, 'header2' AS Source_Element, 'location' AS Source_Value FROM dual
UNION SELECT '2' AS Row_Num, 'header3' AS Source_Element, 'US' AS Source_Value FROM dual
--
UNION SELECT '3' AS Row_Num, 'header1' AS Source_Element, 'Email' AS Source_Value FROM dual
UNION SELECT '3' AS Row_Num, 'header2' AS Source_Element, 'location' AS Source_Value FROM dual
UNION SELECT '3' AS Row_Num, 'header3' AS Source_Element, 'Canada' AS Source_Value FROM dual
);

SELECT row_num
FROM
(
SELECT
     h1.row_num
    ,h1.source_value        AS Header_1
    ,h2.source_value        AS Header_2
    ,h3.source_value        AS Header_3
FROM demo h1
    --
    JOIN demo h2
        ON h2.row_num = h1.row_num
    --
    JOIN demo h3
        ON h3.row_num = h2.row_num
WHERE (1 = 1)
  AND h1.source_element = 'header1'
  AND h2.source_element = 'header2'
  AND h3.source_element = 'header3'
)
WHERE (1 = 1)
  AND header_1 = 'mail'
  AND header_3 = 'US'
;