与鲜明MAX（列值）选择行

Question

[ExactReplica] [FilteredOpportunityProduct表

Opportunityid  baseamount 
1     500 
1     500 
2     600 
2     700 

[ExactReplica] .FilteredOpportunity表

Opportunityid name 
1    ABC 
2    CDF 

我想利用的最大baseamount。然而，现在面临的问题时，存在着baseamount的重复，我怎么可以只有一条记录

我的查询

select 
    MaxAmount.[baseamount] , 
    c.name 


FROM [ExactReplica].FilteredOpportunity c 

Left JOIN 
     (
      SELECT opportunityid, 
        MAX((baseamount)) baseamount 
      FROM [ExactReplica].[FilteredOpportunityProduct] 
      GROUP BY opportunityid 

     ) MaxAmount ON c.opportunityid = MaxAmount.opportunityid 
    inner JOIN 
     [ExactReplica].[FilteredOpportunityProduct] p ON MaxAmount.opportunityid = p.opportunityid 
        AND MaxAmount.baseamount = p.baseamount 

Answer 1

试试这个：

select max(baseamount) baseamount,a.name 
from 
(select 
    baseamount, ROW_NUMBER() over (partition by p.opportunityid,baseamount order by p.baseamount desc) rn, 
    c.name 

FROM FilteredOpportunity c 
    inner JOIN 
     [FilteredOpportunityProduct] p ON c.opportunityid = p.opportunityid) a 
     where rn=1 
     group by a.name 

OUTPUT：

baseamount name 
500   ABC 
700   CDF 

Answer 2

你可以试试以下查询预期水库ULT，我已经执行了下面的脚本：

对于表创建：

CREATE TABLE FILTEREDOPPORTUNITYPRODUCT（ OPPORTUNITYID INT NULL， BASEAMOUNT VARCHAR（24）NULL ）

CREATE TABLE FILTEREDOPPORTUNITY（ OPPORTUNITYID INT NULL， NAME VARCHAR（24）NULL ）

插入：

INSERT INTO FILTEREDOPPORTUNITYPRODUCT（OPPORTUNITYID，BASEAMOUNT）VALUES （1500），（1500），（2600），（2700）

INSERT INTO FILTEREDOPPORTUNITY（OPPORTUNITYID，NAME）VALUES （1， 'ABC'），（ 2， 'CDF'）

选择：

SELECT A.OPPORTUNITYID，B.NAME，MAX（BASEAMOUNT）AS BASEAMOUNT FROM FILTEREDOPPORTUNITYPRODUCT AS甲
JOIN FILTEREDOPPORTUNITY AS乙
ON A.OPPOR TUNITYID = B.OPPORTUNITYID GROUP BY A.OPPORTUNITYID，B.NAME