关于IBAITS 中的 group by

    <resultMap id="gecStrategyRsWithClientName" class="gecStrategyForClone" groupBy="strategyId">
        <result property="strategyId" column="STRATEGY_ID"/>
        <result property="clientId" column="CLIENT_ID"/>
        <result property="strategyName" column="STRATEGY_NAME"/>
        <result property="clientShortName" column="CLIENT_SHORT_NAME"/>                                            
    </resultMap>

 

    <select id="getExistStrategiesForClone" resultMap="gecStrategyRsWithClientName">
        SELECT
            st.STRATEGY_ID,
            st.CLIENT_ID,
            st.STRATEGY_NAME,    
            c.CLIENT_SHORT_NAME   

        FROM GEC_STRATEGY st
        LEFT JOIN GEC_CLIENT c
        ON st.CLIENT_ID = c.CLIENT_ID
        LEFT JOIN GEC_FUND fund
        ON st.STRATEGY_ID = fund.STRATEGY_ID
        WHERE  st.STATUS != 'D'
        ORDER BY lower(c.CLIENT_SHORT_NAME)
    </select>   

 

执行结果是：

 

25    2    130-30    SANTABARB
21    21    130-30    AJO
23    3    130-30    ANALYTIC
24    3    KFC    ANALYTIC
26    2    KFC    SANTABARB
22    21    KFC    AJO

 

如果没有groupBy="strategyId"，则结果是

 

25    2    130-30    SANTABARB
21    21    130-30    AJO
23    3    130-30    ANALYTIC
24    3    KFC    ANALYTIC
26    2    KFC    SANTABARB
22    21    KFC    AJO
22    21    KFC    AJO
22    21    KFC    AJO

 

 

如果一个对象如 GecManualBorrow

 

private BigDecimalreclaimRate;

private BigDecimaloverseaTaxPer;

private BigDecimaldomesticTaxPer;

private BigDecimalprepayRate;

private BigDecimalminimumFee;

private StringpositionFlag;

private StringcommentTxt;

private List<GecManualBorrowAllocation>borrowAllocations;

 

 

<resultMapid="manualBorrowsRs"class="gecManualBorrow"groupBy="borrowId">

<resultproperty="borrowId"column="borrow_id"/>

<resultproperty="assetCode"column="cusip"/>

<resultproperty="brokerCd"column="broker_cd"/>

。。。。

<resultproperty="borrowAllocations"resultMap="GEC.borrowAllocations"/>

 

 

<resultMapid="borrowAllocations"class="gecManualBorrowAllocation">

<resultproperty="counterpartyCd"column="COUNTERPARTY_CD"/>

<resultproperty="prepayDate"column="loan_prepay_date"/>

<resultproperty="netDividend"column="loan_net_div"/>

</resultMap>

 

 

如果没有group by , 则出来的结果中“borrowAllocations” 可能不是个List， 而是一个borrow id 对应一个borrowAllocations， 而正确结果应该是一个borrow id 对应一个 List<borrowAllocations>