MYSQL相关

给查询出的数据增加自增ID

select  (@i:=@i+1) as '排名',emp.emp_id
from commision_assistant_employee as emp,(select @i:=0) as test
where id<100

select  (@i:=@i+1) as '排名',emp.emp_id
from commision_assistant_employee as emp
left join (select @i:=0) as test on 1=1
where id<100

mysql 怎么根据某个字段指定值进行排序

select * from commision_common_con_refund_gh where source = 'JY' and commission_time between unix_timestamp('2021-03-01') and unix_timestamp('2021-03-31 23:59:59') and status = 2 order by source,contract_num,con_id,FIELD(refund_type,'4','3','1','2')

GROUP_CONCAT:多行合并展示
group_concat(from_unixtime(etc.permon,'%Y-%m'),'（',if(etc.is_effect=1,'生效','已失效'),'）' ORDER BY etc.permon SEPARATOR '<br>') as permon

SELECT emp_idcard, sum(tem.male) as male, sum(tem.broker) as broker, sum(tem.assessor) as assessor, sum(tem.broker_refund ) as broker_refund 
            FROM (
                SELECT emp_idcard, extra_type,
                    case when extra_type = 1 then SUM(commission) else 0 end as male,
                    case when extra_type = 2 then SUM(commission) else 0 end as broker,
                    case when extra_type = 3 then SUM(commission) else 0 end as assessor,
                    case when extra_type = 4 then SUM(commission) else 0 end as broker_refund 
                    FROM commision_assistant_extra_reward 
                    WHERE emp_idcard > 0 and commission_time between unix_timestamp('2021-03-01') and unix_timestamp('2021-03-31 23:59:59') AND status = 2 
                    GROUP BY emp_idcard, extra_type) as tem
            GROUP BY tem.emp_idcard