mysql查询总结_mysql查询总结相关

#查询之后添加序号递增一列：

SELECT CONVERT((@rowNum := @rowNum + 1),CHAR(10)) AS 序号,

temp.*

FROM(

SELECT h.applyNo '订单编号',

IFNULL(p.name, c.shortName) '出借人姓名',

h.productNo '产品编号',

pb.`productName` '产品名称',

h.investAmount '订单金额',

h.`payDate` '支付时间',

DATE_FORMAT(er.repayDate, '%Y-%m-%d') '到账时间'

FROM dq_order_header h

LEFT JOIN jz_user u ON h.`uid`=u.`id`

LEFT JOIN `jz_person` p ON h.`uid`=p.`uid`

LEFT JOIN `jz_company` c ON h.uid =c.uid

LEFT JOIN jinzhu.`dq_product_base` pb ON h.`productNo`=pb.`productNo`

LEFT JOIN (SELECT a.applyNo, MAX(a.repayDate) repayDate FROM dq_order_expect_receivable a WHERE a.status!='SETTLED' GROUP BY a.applyNo) er ON h.applyNo=er.applyNo

LEFT JOIN (SELECT COUNT(0) liquidation, a.uid, MAX(a.createDate) liquidationDate FROM `jz_liquidation_record` a WHERE a.contractType IN ('DebtTransContract','DebtTransNotice') GROUP BY a.uid) li ON h.`uid`=li.uid

LEFT JOIN `dq_zz_order_header` zo ON h.zzApplyNo=zo.zzApplyNo

LEFT JOIN `dq_zz_product_info` zp ON zo.zzProductNo = zp.zzProductNo

LEFT JOIN (SELECT COUNT(0) liquidation, a.uid, MAX(a.createDate) liquidationDate FROM `jz_liquidation_record` a WHERE a.contractType IN ('DebtTransContract','DebtTransNotice') GROUP BY a.uid) zli ON zp.uid = zli.uid

WHERE h.`investStatus`='CHECKED'

ORDER BY er.repayDate, h.`payDate`

)temp,(SELECT @rowNum := 0) rn