包含忽略列的内部联接的DISTINCT查询，

Question

我有一个DB2查询如下：

SELECT DISTINCT RETAILMASTERFILE.DOIDCD AS "RETAILMASTERFILE_DOIDCD",
         RETAILMASTERFILE.COCOMO AS "RETAILMASTERFILE_COCOMO",
         #XENOS.CUSTREF AS "XENOS_CUSTREF",
         #XENOS.ADDUDT AS "XENOS_ADDUDT",
         #XENOS.ADUPDD AS "XENOS_ADUPDD",
         #XENOS.ADUPDT AS "XENOS_ADUPDT",
         #XENOS.ADSTAT AS "XENOS_ADSTAT"
FROM     RETAILMASTERFILE INNER JOIN
         #XENOS ON RETAILMASTERFILE.DOCOMP = #XENOS.ADCOMP 
                AND RETAILMASTERFILE.COCOMO = #XENOS.ADDELN 
WHERE     (RETAILMASTERFILE.DOIDCD = 'CUST008') 
      AND (RETAILMASTERFILE.COCOMO = '345126032') 
      AND (RETAILMASTERFILE.DOCOMP = 'LONDON')

问题是#XENOS.ADUPDT可能不是唯一的，这给了我一个不必要的重复记录。我有没有办法把这排除在考虑之外？到目前为止，我在有限的知识和对群体的粗浅理解范围内所做的一切都打破了我的疑问。

Answer 1

使用GROUP BY代替：

SELECT   RETAILMASTERFILE.DOIDCD AS "RETAILMASTERFILE_DOIDCD",
         RETAILMASTERFILE.COCOMO AS "RETAILMASTERFILE_COCOMO",
         #XENOS.CUSTREF AS "XENOS_CUSTREF",
         #XENOS.ADDUDT AS "XENOS_ADDUDT",
         #XENOS.ADUPDD AS "XENOS_ADUPDD",
         MAX(#XENOS.ADUPDT) AS "XENOS_ADUPDT",
         #XENOS.ADSTAT AS "XENOS_ADSTAT"
FROM     RETAILMASTERFILE INNER JOIN
         #XENOS
         ON RETAILMASTERFILE.DOCOMP = #XENOS.ADCOMP AND
            RETAILMASTERFILE.COCOMO = #XENOS.ADDELN 
WHERE  (RETAILMASTERFILE.DOIDCD = 'CUST008') AND (RETAILMASTERFILE.COCOMO = '345126032') AND 
       (RETAILMASTERFILE.DOCOMP = 'LONDON')
GROUP BY RETAILMASTERFILE.DOIDCD,
         RETAILMASTERFILE.COCOMO,
         #XENOS.CUSTREF,
         #XENOS.ADDUDT,
         #XENOS.ADUPDD,
         #XENOS.ADSTAT;