假设我有一个复杂的查询,该查询从许多表中获取数据.

    SELECT
    DISTINCT
        table1.hash, 
        table2.field1,
        table3.field1,
        table4.field1, 
...
...
...
    FROM table1
    inner JOIN table2
    ON table1.hash=device_model.hash 
        and table1.date = table2.date 
        and table1.time = table2.time

...
...
...
    inner JOIN table_n-1
    ON table1.hash=table_n-1.hash 
        and table1.date = table_n-1.date 
        and table1.time = table_n-1.time
    left JOIN table_n
    ON table1.hash=table_n.hash 
        and table1.date = table_n.date 
        and table1.time = table_n.time
...
...
...
    ORDER BY table1.date

多亏了“ DISTINCT”运算符,我得到了三行结果

+---------+----------+------------+
|value1   |Null      | value3     |
+---------+----------+------------+ 
|value1   |Null      | Null       |
+---------+----------+------------+ 
|value1   |value2    | null       |
+---------+----------+------------+ 
|value1   |Null      | value3     |
+---------+----------+------------+ 

所以我每行至少有一个Null.
如何将选择结果合并为一行以获取所有数据？
我想看看：

+---------+----------+------------+ 
|value1   |value2    | value3     |
+---------+----------+------------+

显然,第一行的value3等于最后一行的value3.
因此,结果行仅因Null而不同.

解决方法:

您要的是分组和使用最大聚合函数.假设您有此表(您的查询非常大,可以在此处粘贴：)：

+---------+---------+----------+------------+
|hash     |field1   |field2    | field3     |
+---------+---------+----------+------------+
|hash01   |value1   |Null      | value3     |
|hash01   |value1   |Null      | Null       |
|hash01   |value1   |value2    | null       |
|hash01   |value1   |Null      | value3     |
+---------+---------+----------+------------+

您可以按哈希列分组,并对其余部分应用最大值.这样,在field2中value2优于null,在field3中value3再次优于null.在此示例中,同一哈希有2个value3.因此,max函数仍将起作用.如果同一列中的同一散列有2个不同的值,则您应澄清它们的用途,因为结果中只能选择一个(根据您的示例).

因此,对上述结果的查询将类似于：

select hash, max(field1) as field1, max(field2) as field2, max(field3) as field3
group by hash

将其应用到示例中,很可能意味着您必须像在简化示例中一样,删除distingle子句并选择/分组结果.