sql - 在SELECT DISTINCT查询上需要帮助

我正在使用Firebird 2.1来执行以下查询:

SELECT DISTINCT INVOICES.INVNO,INVOICES.DEPNO,INVOICES.INVN,INVOICES.CONTNO,SYSPROF.NAME
FROM INVOICES
LEFT JOIN INVSPEC ON (INVOICES.INVNO = INVSPEC.INVNO) AND (INVOICES.DEPNO = INVSPEC.DEPNO)
LEFT JOIN SYSPROF ON (INVSPEC.SYSNO = SYSPROF.SYSNO) WHERE
(INVOICES.INVNO = 17 AND INVOICES.DEPNO = 6555)
OR(INVOICES.INVNO = 16 AND INVOICES.DEPNO = 6555);

它向我返回这些记录:

请帮助我建立一个查询，以返回以下记录:

谢谢!

最佳答案

例如:

SELECT INVOICES.INVNO,min(INVOICES.DEPNO),min(INVOICES.INVN),min(INVOICES.CONTNO),min(SYSPROF.NAME)
FROM INVOICES
LEFT JOIN INVSPEC ON (INVOICES.INVNO = INVSPEC.INVNO) AND (INVOICES.DEPNO = INVSPEC.DEPNO)
LEFT JOIN SYSPROF ON (INVSPEC.SYSNO = SYSPROF.SYSNO) WHERE
(INVOICES.INVNO = 17 AND INVOICES.DEPNO = 6555)
OR(INVOICES.INVNO = 16 AND INVOICES.DEPNO = 6555);
GROUP BY INVOICES.INVNO

该查询每张发票返回一行，并使用sysprof中的任何名称。

关于sql - 在SELECT DISTINCT查询上需要帮助，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12257237/