只从一列中选择不同的值

Question

我正在显示查询中的三个字段，并希望显示一个不同的banking_no。我仍然想显示其他字段，即使它们是不明确的。请帮帮忙。

SELECT  C.RECEIPT_OFFICE_PREFIX, B.BANKING_NO, B.STATUS_CD
FROM    TControl B, TComponent C
WHERE   C.DEPOSIT_BANK_ACCT = 'xxx-xxxxxx-xxxxx'
AND     B.BANKING_NO = C.BANKING_NO
AND     B.COMPANY_ID = C.COMPANY_ID
AND     B.RECEIPT_OFFICE_PREFIX = C.RECEIPT_OFFICE_PREFIX
AND     B.STATUS_CD != 'C'
ORDER BY B.BANKING_NO

我正在使用Sybase ASE 12.5

Answer 1

更多最新版本的Sybase支持row_number()。您可以在这样的子查询中使用它：

SELECT RECEIPT_OFFICE_PREFIX, BANKING_NO, STATUS_CD
FROM (SELECT C.RECEIPT_OFFICE_PREFIX, B.BANKING_NO, B.STATUS_CD,
             ROW_NUMBER() OVER (PARTITION BY B.BANKING_NO ORDER BY B.BANKING_NO) as seqnum
      FROM TControl B JOIN
           TComponent C
           ON B.BANKING_NO = C.BANKING_NO AND B.COMPANY_ID = C.COMPANY_ID
      WHERE C.DEPOSIT_BANK_ACCT = 'xxx-xxxxxx-xxxxx' AND     
            B.RECEIPT_OFFICE_PREFIX = C.RECEIPT_OFFICE_PREFIX AND
            B.STATUS_CD != 'C'
    ) BC
WHERE seqnum = 1
ORDER BY BANKING_NO;

Answer 2

你想要的东西可能不会被称为“不愉快”。如果其余的数据是相同的，只使用一个不同的就行了。但是，如果数据可能不同，则需要说明数据库应该选择什么结果。第一排？最后一排？

如果你不介意的话就用吧；

SELECT DISTINCT  MAX(C.RECEIPT_OFFICE_PREFIX), B.BANKING_NO, MAX(B.STATUS_CD)
FROM    TControl B, TComponent C
WHERE   C.DEPOSIT_BANK_ACCT = 'xxx-xxxxxx-xxxxx'
AND     B.BANKING_NO = C.BANKING_NO
AND     B.COMPANY_ID = C.COMPANY_ID
AND     B.RECEIPT_OFFICE_PREFIX = C.RECEIPT_OFFICE_PREFIX
AND     B.STATUS_CD != 'C'
GROUP BY B.BANKING_NO

在您需要选择一行使用戈登解决方案。