SELECT * , group_concat( ' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , ' ' ),group_concat(' ' , `order_id` , ' ') FROM ( select * from `sale_orders` order by `sku`) T WHERE `shipments` =0 AND `number` =$id AND `delivery_areas` ='HK' GROUP BY `recipient-name` , `ship-address-1` ORDER BY length(group_concat( ' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , ' ' )) DESC,`sku`
结果:
buyer-phone-number sku-quantity order_id
8034008750 HK-A0030-BK-RE-20 X 1 , HK-A0119-BK-GM-19 X 1 250-3461812-0567023 , 250-1605177-6035013
079-233-4110 HK-A0041-00-WW-25 X 1 , HK-A0256-00-O6-34 X 1 250-4437711-2084643 , 250-4437711-2084643
0263-50-5751 HK-A0041-00-WW-25 X 1 , HK-A0217-00-SI-33 X 1 250-9750167-8343033 , 250-9750167-8343033
9064294166 HK-A0069-BK-G6-18 X 1 , HK-A0235-01-KW-51 X 1 249-9717753-8795846 , 249-3146806-7811263
03-+58008649 HK-A0079-BK-I2-46 X 1 , HK-A0265-00-UC-61 X 1 249-3475969-4054212 , 249-3475969-4054212
sku-quantity这个字段的内容是SKU(物料编码) X quantity(数量)我想实际的功能是如果数量大于1 ,内容为
*****HK-A0079-BK-I2-46 X 2,HK-A0235-01-KW-51 X 1
就是在现有的基础上加“****”以下为我想实现的效果
HK-A0079-BK-I2-46 X 1 , *****HK-A0265-00-UC-61 X 2 , HK-A0265-00-UC-61 X 1
HK-A0079-BK-I2-46 X 1 , *****HK-A0265-00-UC-61 X 2 , *****HK-A0265-00-UC-61 X 2请问怎么实现呢?
结果:
buyer-phone-number sku-quantity order_id
8034008750 HK-A0030-BK-RE-20 X 1 , HK-A0119-BK-GM-19 X 1 250-3461812-0567023 , 250-1605177-6035013
079-233-4110 HK-A0041-00-WW-25 X 1 , HK-A0256-00-O6-34 X 1 250-4437711-2084643 , 250-4437711-2084643
0263-50-5751 HK-A0041-00-WW-25 X 1 , HK-A0217-00-SI-33 X 1 250-9750167-8343033 , 250-9750167-8343033
9064294166 HK-A0069-BK-G6-18 X 1 , HK-A0235-01-KW-51 X 1 249-9717753-8795846 , 249-3146806-7811263
03-+58008649 HK-A0079-BK-I2-46 X 1 , HK-A0265-00-UC-61 X 1 249-3475969-4054212 , 249-3475969-4054212
sku-quantity这个字段的内容是SKU(物料编码) X quantity(数量)我想实际的功能是如果数量大于1 ,内容为
*****HK-A0079-BK-I2-46 X 2,HK-A0235-01-KW-51 X 1
就是在现有的基础上加“****”以下为我想实现的效果
HK-A0079-BK-I2-46 X 1 , *****HK-A0265-00-UC-61 X 2 , HK-A0265-00-UC-61 X 1
HK-A0079-BK-I2-46 X 1 , *****HK-A0265-00-UC-61 X 2 , *****HK-A0265-00-UC-61 X 2请问怎么实现呢?
mysql> select * from sku;
+-------------------+----------+
| sku | quantity |
+-------------------+----------+
| HK-A0079-BK-I2-46 | 1 |
| HK-A0265-00-UC-61 | 2 |
| HK-A0265-00-UC-61 | 1 |
| HK-A0079-BK-I2-46 | 1 |
| HK-A0265-00-UC-61 | 2 |
| HK-A0265-00-UC-61 | 2 |
+-------------------+----------+
6 rows in set (0.00 sec)mysql> select group_concat(if(quantity > 1, "****" , " "), sku, " X ", quantity) as "sku-quantity"
-> from sku;
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| sku-quantity |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| HK-A0079-BK-I2-46 X 1,****HK-A0265-00-UC-61 X 2, HK-A0265-00-UC-61 X 1, HK-A0079-BK-I2-46 X 1,****HK-A0265-00-UC-61 X 2,****HK-A0265-00-UC-61 X 2 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql>
#1558 - Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50077, now running 50158. Please use mysql_upgrade to fix this error.