如何从表中选择 * 并按单列分组

Question

我想从几个表中选择 *，但不写出所有列名，但要消除重复项。合并表中大约有 30 列，我不想写出每一列。

我知道我可以写出所有列名，但我想看看是否有办法使用 select * 来做到这一点

SELECT
        a.* 
FROM "T1"."T2"."T3" a
LEFT JOIN "T1"."T2"."T4" b on b.id = a.id
LEFT JOIN "T1"."T2"."T5" c on c.id = a.id
LEFT JOIN "T1"."T2"."T6" d on d.id = a.id
WHERE a.org = '123' AND kind = 'abc' 

有没有办法按 a.id 分组而不必写出每个列名？最终表中有 41 列，我试图只在 id 上返回不同的行。

Answer 1

不完全是group by，但row_number()：

SELECT a.*
FROM (SELECT a.*,
             ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY a.id) as seqnum
      FROM "T1"."T2"."T3" a LEFT JOIN
           "T1"."T2"."T4" b 
           ON b.id = a.id LEFT JOIN
           "T1"."T2"."T5" c
           ON c.id = a.id LEFT JOIN
           "T1"."T2"."T6" d 
           ON d.id = a.id
      WHERE a.org = '123' AND kind = 'abc' 
     ) a
WHERE seqnum = 1;