写了一个不错的sql（学习到很多）

下面就是这个这个sql：

select
        (@i:=@i+1) as id,
         b.ypmc as medName,
        case when  b.p_unit = '克' then '公斤'
        else b.p_unit end as p_unit,
        b.price,
        round(sum(b.sum_quantit)/1000,5) as sum_quantit,
        round(b.price*sum(b.sum_quantit)/1000,2) as total,
        b.ea_id,
        b.line,
        b.pydm from(
        select      yhds.ypmc,
        case when REPLACE(ypd.p_unit,' ','') = 'g' then '克'
        when REPLACE(ypd.p_unit,' ','') = 'G' then '克'
        else REPLACE(ypd.p_unit,' ','') end as p_unit,
        round(yhds.pfj*yhds.coef,2) as price ,
        ypd.quantity as sum_quantit,
        yhds.ea_id as ea_id,
        yhds.line as line,
        yhds.pydm as pydm
        from yc_order as yo
        left join yc_prescription as yp
        on yo.p_id = yp.id
        left join yc_prescription_detail as ypd
        on yp.med_id = ypd.serial_number_id
        left join yc_his_drug_store yhds
        on yhds.ea_id  = (SELECT SUBSTRING_INDEX(ypd.med_code, "_", 1))
        where yo.push_time is not null
        <if test="startTime != null and startTime != '' and endTime != null and endTime != '' and dataType==1">
            and yo.push_time between #{startTime} and #{endTime}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != '' and dataType==2">
            and yo.create_time between #{startTime} and #{endTime}
        </if>
        <if test="name!=null and name !=''">
            and yhds.ypmc like concat('%',#{name},'%')
        </if>
        <if test="fId!=null">
            and yo.f_id = #{fId}
        </if>
        )  b,(select @i:=0)   as   it
        GROUP BY ea_id
        HAVING ea_id is not null
        order by id

首先需要导出的表格中有id序号排列，这里我定义了一个@i 让这个@i自增，所以就得到的自增的id
用法：

select (@i:=@i+1),* from table ,  (select @i:=0)   as   it

其次就是函数进行计算，可以进行任何的计算 并保留小数点后几位

round(sum(b.sum_quantit)/1000,5)   

把某个符号换成另一个东西 这里还进行了case的转换

case when REPLACE(ypd.p_unit,' ','') = 'g' then '克'
        when REPLACE(ypd.p_unit,' ','') = 'G' then '克'
        else REPLACE(ypd.p_unit,' ','') end as p_unit

截取某个字符串前几位，这里的格式为1234_564_789

left join yc_his_drug_store yhds
        on yhds.ea_id  = (SELECT SUBSTRING_INDEX(ypd.med_code, "_", 1))