mysql查询生成序号键(用于多表联合查询时选择导出主键)

在多表联合查询时,由于多表union all 无法确定查询记录的唯一id,因而在选择导出数据时往往会出现主键不唯一的情况,所以,我们可以采取数据库随机生成唯一序号作为主键;其sql案例如下:

<select id="getReceDetailGridData" resultType="com./.bo.ChartAnalysisReceDetailBO">

    SELECT allTh.* FROM(
    SELECT @rownum:=@rownum+1 AS rowNum,allTables.*
    FROM (SELECT @rownum:=0) r,(
    SELECT DISTINCT
    IFNULL(d.bill_type,99) AS billType,
    convert(b.total_fee/100,DECIMAL(10,2)) AS floatMoney,
    b.total_fee AS money,
    IFNULL(d.remark,'')AS remark,
    IFNULL(a.phone,'') AS nameOrPhone,
    IFNULL(a.in_time,'') AS inTime,
    IFNULL(a.out_time,'') AS outTime,
    IFNULL(a.park_period_time,'') AS parkPeriodTime,
    b.plate_no plateNo,
    b.`status` parkStatus

    FROM
    park_record a
    LEFT JOIN park_charge c ON c.record_id= a.record_id
    LEFT JOIN receivable d ON d.bill_no= c.bill_no,
    arrears_record b

    WHERE
    b.total_fee>0
    AND a.park_id = b.park_id
    AND a.in_unid = b.unid
    AND a.dealer_id = b.dealer_id
    AND a.out_time BETWEEN #{bgnTime,jdbcType=TIMESTAMP} AND #{endTime,jdbcType=TIMESTAMP}
    <if test="dealerId!=null and dealerId !=''">
        AND a.dealer_id = #{dealerId,jdbcType=BIGINT}
    </if>

    UNION ALL

    SELECT

    99 AS billType,
    convert(a.money/100,DECIMAL(10,2)) AS floatMoney,
    a.money ,
    '漏费账单' AS remark,
    '' AS nameOrPhone,
    a.int_time AS inTime,
    a.out_time AS outTime,
    TIMESTAMPDIFF(MINUTE,a.int_time,a.out_time) AS parkPeriodTime,
    '' AS plateNo,
    a.`status` parkStatus

    FROM
    berth_charge_record a
    WHERE
    a.`status` = 0
    AND a.out_time BETWEEN #{bgnTime,jdbcType=TIMESTAMP} AND #{endTime,jdbcType=TIMESTAMP}
    <if test="dealerId!=null and dealerId !=''">
        AND a.dealer_id = #{dealerId,jdbcType=BIGINT}
    </if>

    UNION ALL

    SELECT

    b.bill_type AS billType,
    convert(b.money/100,DECIMAL(10,2)) AS floatMoney,
    b.money ,
    IFNULL(b.remark,'')AS remark,
    IFNULL(e.mobile_phone,'') AS nameOrPhone,
    '' AS inTime,
    '' AS outTime,
    '' AS parkPeriodTime,
    '' AS plateNo,
    b.`status` parkStatus

    FROM
    received a,
    receivable b
    LEFT JOIN sys_user e ON e.user_id=b.user_id,
    bag_period_charge c

    WHERE a.receivable_id = b.receivable_id
    AND b.bill_type = 2
    AND b.bill_no = c.bill_no
    AND b.`status`=2
    AND a.pay_time BETWEEN #{bgnTime,jdbcType=TIMESTAMP} AND #{endTime,jdbcType=TIMESTAMP}
    <if test="dealerId!=null and dealerId !=''">
        AND c.dealer_id = #{dealerId,jdbcType=BIGINT}
    </if>
    ) allTables
    <where>
        <if test="billType != null  and ''!= billType and  '99' != billType ">
            AND billType= #{billType,jdbcType=TINYINT}
        </if>
        <if test="billType != null  and  '99' == billType ">
            AND billType=99
            AND parkStatus=0
        </if>
        <if test="nameOrPhoneStr != null and '' != nameOrPhoneStr">
            AND nameOrPhone LIKE CONCAT('%',#{nameOrPhoneStr},'%')
        </if>
        <if test="plateNo != null and '' != plateNo">
            AND plateNo = #{plateNo,jdbcType=VARCHAR}
        </if>
    </where>
    )allTh
    <where>
        <if test="rowIds!=null and rowIds !=''">
            AND rowNum in (${rowIds})
        </if>
    </where>

</select>

 

把rowNum作为表格的rowId,就可以实现主键唯一导出数据了;