返回与postgresql的行的连接

Question

在我的桌子上我有这样的情况

|  id    |       book                      | reg   |    cif                       | nm  |    end        | nr |aa | bb  |    type     | 
|12134357|"Lv.2-Registro Geral (matrícula)"|"10001"|"1002414002024400386-90705793"|     |"Ataulfo Alves"|1877|"G"|"102"|             |
|12134356|"Lv.2-Registro Geral (matrícula)"|"10001"|""                            |"RUA"|"Ataulfo Alves"|1877|"G"|"102"|"Apartamento"|
|12134358|"Lv.2-Registro Geral (matrícula)"|"10001"|""                            |""   |"Ataulfo Alves"|    |"" |""   |"Apartamento"|

可以使用一个select(group by book and reg I reg仅返回一行所有信息？如果我使用DISTINCT，她将对每个相等的值进行分组。

结果示例：

|"Lv.2-Registro Geral (matrícula)"|"10001"|"1002414002024400386-90705793"|"RUA|"Ataulfo Alves"|1877|"G"|"102"|"Apartamento"|

tks

Answer 1

SELECT book,
       MAX("reg"), 
       MAX("cif"), 
       MAX("nm"), 
       MAX("end"), 
       MAX("nr"),
       MAX("aa"),
       MAX("bb"),
       MAX("type")
FROM  table1
GROUP BY book;

输出

|                              book |                        max |                            max |   max |             max |  max | max |   max |           max |
|-----------------------------------|----------------------------|--------------------------------|-------|-----------------|------|-----|-------|---------------|
| "Lv.2-Registro Geral (matrícula)" | January, 01 10001 00:00:00 | "1002414002024400386-90705793" | "RUA" | "Ataulfo Alves" | 1877 | "G" | "102" | "Apartamento" |

Answer 2

我不确定你到底想要做什么，但是下面这个(非常难看的)查询将会找到每个字段的第一个非空值，并一次性返回所有的值：

SELECT
    (SELECT "id"   FROM mytable WHERE coalesce(length("id"   || '') > 0, false) ORDER BY id LIMIT 1),
    (SELECT "book" FROM mytable WHERE coalesce(length("book" || '') > 0, false) ORDER BY id LIMIT 1),
    (SELECT "reg"  FROM mytable WHERE coalesce(length("reg"  || '') > 0, false) ORDER BY id LIMIT 1),
    (SELECT "cif"  FROM mytable WHERE coalesce(length("cif"  || '') > 0, false) ORDER BY id LIMIT 1),
    (SELECT "nm"   FROM mytable WHERE coalesce(length("nm"   || '') > 0, false) ORDER BY id LIMIT 1),
    (SELECT "end"  FROM mytable WHERE coalesce(length("end"  || '') > 0, false) ORDER BY id LIMIT 1),
    (SELECT "nr"   FROM mytable WHERE coalesce(length("nr"   || '') > 0, false) ORDER BY id LIMIT 1),
    (SELECT "aa"   FROM mytable WHERE coalesce(length("aa"   || '') > 0, false) ORDER BY id LIMIT 1),
    (SELECT "bb"   FROM mytable WHERE coalesce(length("bb"   || '') > 0, false) ORDER BY id LIMIT 1),
    (SELECT "type" FROM mytable WHERE coalesce(length("type" || '') > 0, false) ORDER BY id LIMIT 1);