模拟案例，订单详情表，后台要有界面汇总订单详情里面所有品的数量情况

CREATE TABLE `order_detail`  (

  `detail_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  `good_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,

  `good_sku` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,

  `number` int(11) NULL DEFAULT NULL,

  PRIMARY KEY (`detail_id`) USING BTREE

) ENGINE = MyISAM AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

INSERT INTO `order_detail` VALUES (1, '苹果', 'pingguo', 1);

INSERT INTO `order_detail` VALUES (2, '香蕉', 'xiangjiao', 1);

INSERT INTO `order_detail` VALUES (3, '苹果', 'pingguo', 2);

INSERT INTO `order_detail` VALUES (4, '鸭梨', 'yali', 5);

INSERT INTO `order_detail` VALUES (5, '香蕉', 'xiangjiao', 1);

 

原生的sql查询：

select good_sku,sum(number) as count  from order_detail   where 1=1     group by good_sku having count>=1  ORDER BY count  desc   limit  0,100

最终统计到所有产品数量情况，根据数量进行备货。

 下面这是一个实际项目中的例子：

根据订单主表order_status过滤状态，比如订单是待发货状态的的所有产品的备货量统计情况。

select    goodsku,sum(number) as count  from client_order_orderdetail a  left join client_order_info b  on a.order_sn=b.order_sn  where a.clientkeynum='E22F093688D696CCC166E14FD872BFA1' and b.order_status=2    group by a.goodsku having count>1  ORDER BY count  desc   limit  0,100

select    *,count(cardnum) as count  from client_merchant_kace_card_uselog  group by cardnum having count>1  ORDER BY count