在SQL Server中与众不同 [英] Distinct in SQL Server

问题描述

我正在执行以下查询，

Select distinct
  a.cr_id,
  Case 
    When ca.ca_vote = 'Approve' and ca.ca_title='MANAGER' Then ca.ca_email
    When ca.ca_vote = 'Reject' Then ''
    When ca.ca_vote = 'Pending' Then ''
    When ca.ca_vote = 'IN PROCESS' Then ''
  End as ca_email
from
  credit a
  inner join credit_approvals ca on ca.c_id=a.cr_id
where 
  a.cr_cs_date between Convert(varchar(20),'11/16/2011',101) and dateadd(day,1,convert  (varchar(20),'11/16/2011',101))
order by
  a.cr_id

尽管对于 cr_id 有区别，它仍显示重复的值。请让我知道如何处理，这样我就只能显示不同的记录。

Despite distinct for cr_id, it is still displaying the duplicate values. Please let me know how to handle this, so that I could able to display only distinct records.

推荐答案

Distinct 应用于所有列，而不是紧随 Distinct 之后的列。

Distinct is applied to all columns, not the one which is immediately after Distinct.

如果您有几个不同的 ca_email 来获取 cr_id ，则将全部看到。

If you have several different ca_email for a cr_id, you will see them all.

如果您不希望这样做，则必须想出一个规则来确定重复项中必须保留的记录。

If you don't want that, you have to come up with a rule to decide what record among the duplicates must stay.

这篇关于在SQL Server中与众不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！