PHP菜鸟博客_共同学习分享PHP技术心得【PHP爱好者】
重复订单汇总产品名称和数量用于统计损失量
2024-2-1 菜鸟站长


场景客户通过表格把订单导入系统,重复导入了,然后删除了重复的订单,但是供应商端没有限制住下载了已经删除的订单,并且完成了发货操作。



比如一个订单出现了3次,实际上就是多发货了2单,需要去掉一单然后在统计,下面sql中去掉了order_id最小的那条记录,当然也可以去掉order_id最大的记录。



CREATE TEMPORARY TABLE temp_table AS SELECT

ori_ordernumber,

ori_ordernumber_son,

MIN( order_id ) AS min_order_id,

GROUP_CONCAT( order_id ORDER BY order_id SEPARATOR ',' ) AS order_ids,

REPLACE ( GROUP_CONCAT( order_id ORDER BY order_id SEPARATOR ',' ), CONCAT( MIN( order_id ), ',' ), '' ) AS order_ids_new,

COUNT(*) AS count 

FROM

client_order_info 

WHERE

clientkeynum = 'E22F093688D696CCC166E14FD872BFA1' 

AND is_check = 1 

AND is_daochu = 1 

AND order_status != 5 

AND ori_ordernumber != '' 

GROUP BY

ori_ordernumber,

ori_ordernumber_son 

HAVING

COUNT(*) > 1;-- 然后从临时表中查询并拼接order_ids_new字段

 SELECT

GROUP_CONCAT( order_ids_new ORDER BY min_order_id SEPARATOR ',' ) AS concatenated_order_ids_new 

FROM

temp_table;



查询到的数据如下



上面sql查询出来的数据会少6条,不知道为啥409025,409026,409027,415482,415244,415271,416726,416728,416715,418564,418565,418073,418089,418104,418105,421410,425218,422658,422659,492270,493349,493352,538216,493106,493107,493108,495539,500066,501667,501668,501669,501670,501671,535657,535658,535659,535660,535661,535662,521745,521746,521747,590171,590172,596535,616733,618690,618731,618732,631275,645737,654053,651737,651738,656218,668647,660511,667591,857518,857521,857520,857519,857522,857524,857523,885715,885731,885730,882389,885729,886653,888334,890403,891435,894153,903288,903289,903290,903291,905034,907886,915159,919019,920266,941309,946584,956450,956451,966485,968943,982918,983738,986045,991495,998355,998356,998358,998876,998877,998878,998879,998880,998881,998882,998883,998884,998885,998886,998888,998889,998890,998891,1002757,1002783,1002786,1002797,1002754,1002756,1002759,1002761,1002762,1002763,1002764,1002765,1002766,1002767,1002768,1002769,1002771,1002772,1002774,1002776,1002778,1002779,1002781,1002782,1002785,1002788,1002790,1002792,1002794,1002796,







去掉上面的字符串最后一个逗号,然后继续,下面的汇总就是产品+数量的汇总。







SELECT goodsinfo, SUM(g_num) AS total_g_num

FROM client_order_info

WHERE order_id IN (select order_id from client_order_info where order_id  in  (409025,409026,409027,415482,415244,415271,416726,416728,416715,418564,418565,418073,418089,418104,418105,421410,425218,422658,422659,492270,493349,493352,538216,493106,493107,493108,495539,500066,501667,501668,501669,501670,501671,535657,535658,535659,535660,535661,535662,521745,521746,521747,590171,590172,596535,616733,618690,618731,618732,631275,645737,654053,651737,651738,656218,668647,660511,667591,857518,857521,857520,857519,857522,857524,857523,885715,885731,885730,882389,885729,886653,888334,890403,891435,894153,903288,903289,903290,903291,905034,907886,915159,919019,920266,941309,946584,956450,956451,966485,968943,982918,983738,986045,991495,998355,998356,998358,998876,998877,998878,998879,998880,998881,998882,998883,998884,998885,998886,998888,998889,998890,998891,1002757,1002783,1002786,1002797,1002754,1002756,1002759,1002761,1002762,1002763,1002764,1002765,1002766,1002767,1002768,1002769,1002771,1002772,1002774,1002776,1002778,1002779,1002781,1002782,1002785,1002788,1002790,1002792,1002794,1002796))

GROUP BY goodsinfo

ORDER BY total_g_num DESC;



11.png

发表评论:
昵称

邮件地址 (选填)

个人主页 (选填)

内容