sql 选择表的多列

Question

我正在尝试从 Oracle DB 中的表中进行选择。

如果我在单列上的 select 语句中执行不同操作，则效果很好，但对于多列，这似乎不起作用。

例如。

select DISTINCT col1, col2, col3, col4, col5, col6 from table1

返回相同的结果。

select * from table1

第 2 列、第 3 列有重复值。我想要的是上面所有列值的值，但不是column2和column3的重复值。

你能帮忙吗？我们将不胜感激您的帮助。

谢谢

Answer 1

DISTINCT 消除了所选每行和列的冗余数据。因此，如果您使用 DISTINCT 选择表的所有列，则只有列包含完全相同数据的行才会被聚合。

Answer 2

尝试以下操作：

SELECT DISTINCT COL1 AS COL1, NULL AS COL2, NULL AS COL3, NULL AS COL4,
       NULL AS COL5, NULL AS COL6 FROM TABLE1
UNION ALL
SELECT NULL AS COL1, DCOL2 AS COL2, NULL AS COL3, NULL AS COL4,
       NULL AS COL5, NULL AS COL6 FROM (SELECT DISTINCT COL2 AS DCOL2
                                          FROM TABLE1)
UNION ALL
SELECT NULL AS COL1, NULL AS COL2, DCOL3 AS COL3, NULL AS COL4,
       NULL AS COL5, NULL AS COL6 FROM (SELECT DISTINCT COL3 AS DCOL3
                                          FROM TABLE1)
UNION ALL
SELECT NULL AS COL1, NULL AS COL2, NULL AS COL3, DCOL4 AS COL4,
       NULL AS COL5, NULL AS COL6 FROM (SELECT DISTINCT COL4 AS DCOL4
                                          FROM TABLE1)
UNION ALL
SELECT NULL AS COL1, NULL AS COL2, NULL AS COL3, NULL AS COL4,
       DCOL5 AS COL5, NULL AS COL6 FROM (SELECT DISTINCT COL5 AS DCOL5
                                           FROM TABLE1)
UNION ALL
SELECT NULL AS COL1, NULL AS COL2, NULL AS COL3, NULL AS COL4,
       NULL AS COL5, DCOL6 AS COL6 FROM (SELECT DISTINCT COL6 AS DCOL6
                                           FROM TABLE1);

分享并享受。