重复订单汇总产品名称和数量用于统计损失量
场景客户通过表格把订单导入系统,重复导入了,然后删除了重复的订单,但是供应商端没有限制住下载了已经删除的订单,并且完成了发货操作。
比如一个订单出现了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;
版权声明:若无特殊注明,本文皆为《菜鸟站长》原创,转载请保留文章出处。
本文链接:重复订单汇总产品名称和数量用于统计损失量 - https://wlphp.com/?post=434