mysql经典sql

1、删除排名不是第一的数据，去重复数据

with cte as ( select *,ROW_NUMBER()over( PARTITION by CHARGE_ID,ChargeFlag order by CHARGE_ID) as num from t_inf_hischarginfo where ChargTime >= '2024-09-01 00:00:00' and ConsFlag=0 )

-- select * from t_inf_hischarginfo where id in (select id from cte where num >=2 )

update t_inf_hischarginfo set IsDeleted=1 where id in (select id from cte where num >=2 )

 

 

2、 供应商消耗top10

select * from

(

select cc.auditYear '年份',(ROW_NUMBER() OVER (PARTITION BY cc.audityear ORDER BY cc.sumAmount Desc)) as '排名', cc.SupplierName '供应商名称',cc.sumAmount '总金额' from

(

select

aa.auditYear, aa.SupplierName,(ifnull(aa.amount,0)-ifnull(bb.amount,0)) sumAmount from

(

select year(a.AuditOn) auditYear,b.SupplierName,

sum(ifnull(( CASE VoucherClass WHEN 1 THEN -b.ConsCount ELSE b.ConsCount END ),0)*ifnull(b.Price,0)) amount

from t_oper_consvoucher a

inner join consvoucherdetail b on a.VoucherCode=b.VoucherCode

where a.IsDeleted=0 and b.IsDeleted=0 and a.status=5 and a.AuditOn>'2023-03-01' and a.AuditOn<'2024-08-31 23:59:59'

 

group by b.SupplierName,year(a.AuditOn)

 

) aa

left join

(

select year(a.AuditOn) auditYear,b.suppliername,sum(ifnull(b.ConsRetCount,0)*ifnull(b.price,0)) amount from t_oper_consretvoucher a

inner join consretvoucherDetail b on a.VoucherCode=b.VoucherCode

where a.IsDeleted=0 and b.IsDeleted=0 and a.status=5 and a.AuditOn>'2023-03-01' and a.AuditOn<'2024-08-31 23:59:59'

 

group by b.SupplierName,year(a.AuditOn)

) bb

on aa.suppliername =bb.suppliername and aa.auditYear=bb.auditYear

 

order by aa.auditYear, (ifnull(aa.amount,0)-ifnull(bb.amount,0)) desc

) cc

) dd

where dd.排名<=10