<select id="findList" resultType="KxNissanCar"><!-- (@i:=@i+1) as number , -->
select (@i:=@i+1) as number , t.* from ( SELECT
'上海' as province,
'上海' as city,
d.company_name ,
a.car_no ,
b.maker_brand ,
b.year_family ,
e.item_3301 ,
b.ASK_START_TIME ,
CASE trade.custom_contract_status
WHEN 5 THEN '成交'
WHEN 51 THEN '成交'
WHEN 6 THEN '成交'
WHEN 9 THEN '失败'
ELSE '待成交'
end as custom_contract_status,
trade.custom_trade_price ,
b.BARGAIN_PRICE ,
trade.cnboss_audit_time
FROM
xinche.t_cust_book a
INNER JOIN kx_big_customer c on c.real_car_no = a.car_no
and c.channel_sub_no in ('28676','28154','28219','28568','28569','28571','28573','28574','28576','28577','28189','28185')
and c.create_time >= '2020-01-01 00:00:00'
INNER JOIN kx_big_customer_channel d on c.channel_sub_no = d.channel_no
INNER JOIN xinche.t_ask_price_info b on a.check_no = b.CHECK_NO
and b.ASK_START_TIME >= '2020-01-01 00:00:00'
INNER JOIN xinche.t_check_result_snapshot e on a.check_no = e.check_no
LEFT JOIN xinche_contract_new.t_customer_trade trade on a.car_no = trade.car_no
and b.ASK_PRICE_NO = trade.ask_price_no
and trade.channel in ('28676','28154','28219','28568','28569','28571','28573','28574','28576','28577','28189','28185')
<where>
1 =1
<if test="companyNo != null and companyNo != ''">
AND a.channel = #{companyNo}
</if>
<if test=" carNo != null and carNo != ''">
AND a.car_no like "%"#{carNo}"%"
</if>
<if test='customContractStatus != null and customContractStatus == "成交" '>
AND trade.custom_contract_status in ( 5 , 6 , 51)
</if>
<if test='customContractStatus != null and customContractStatus == "失败" '>
AND trade.custom_contract_status in ( 9 )
</if>
<if test='customContractStatus != null and customContractStatus == "待成交" '>
AND ( trade.custom_contract_status not in ( 5 , 6 , 51 ,9 ) or trade.custom_contract_status is null )
</if>
<if test="askStartTimeBegin != null and askStartTimeBegin != ''">
and b.ASK_START_TIME >= #{askStartTimeBegin}
</if>
<if test="askStartTimeEnd != null and askStartTimeEnd != ''">
and b.ASK_START_TIME < date_add(#{askStartTimeEnd}, INTERVAL 1 day)
</if>
<if test="cnbossAuditTimeBegin != null and cnbossAuditTimeBegin != ''">
and trade.cnboss_audit_time >= #{cnbossAuditTimeBegin}
</if>
<if test="cnbossAuditTimeEnd != null and cnbossAuditTimeEnd != ''">
and trade.cnboss_audit_time < date_add(#{cnbossAuditTimeEnd}, INTERVAL 1 day)
</if>
</where>
) t ,(select @i:=0) as it order by t.ASK_START_TIME desc
</select>
-
无分组
select @rownum:=@rownum+1 as autoSeq,
sid,pid,oprimary,seq
from singlechoice,(select @rownum:=0) r
order by pid,sid
-
单字段分组
select (@i := case when @tableName=a.tablename then @i + 1 else 1 end ) as rowIndex,
a.*,(@tableName:=a.tablename)
from tablestyle a,(select @i:=0,@tableName:='') as t
group by tablename,fieldname
order by tablename,(@i := case when @tableName=a.tablename then @i + 1 else 1 end )
-
多字段分组
select (@i := case when @tableName=concat(a.dbname,a.tablename) then @i + 1 else 1 end ) as rowIndex,
a.*,(@tableName:=concat(a.dbname,a.tablename)) as temp
from tablestyle a,(select @i:=0,@tableName:='') as t
group by dbname,tablename,fieldname
order by dbname,tablename,(@i := case when @tableName=concat(a.dbname,a.tablename) then @i + 1 else 1 end )