mysql分组查询后分页

  • 内容
  • 评论
  • 相关

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

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);

 

图片4.png

原生的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

图片5.png

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

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

根据订单主表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

本文标签:

版权声明:若无特殊注明,本文皆为《菜鸟站长》原创,转载请保留文章出处。

本文链接:mysql分组查询后分页 - http://wlphp.com/?post=373

发表评论

电子邮件地址不会被公开。 必填项已用*标注