常用 mysql 实例总结（待续）

1、group by 与count 连用：按照ID分组，统计每个ID重复了几次

SELECT yww_test.id,COUNT(id) as n FROM yww_test  GROUP BY yww_test.id

表：      查询结果： 

如果想把重复的找出来，还可以：

SELECT id,COUNT(*) AS n   FROM yww_test GROUP BY yww_test.id  HAVING COUNT(*)>1;

2、group by 与sum 函数连用：按照商品编号分组后，对每个编号的商品数量进行求和

SELECT cat.name,g.name, g.batch_no, count( g.batch_no) ,sum(og.num)
FROM `ims_sqtg_sun_order` `o`
LEFT JOIN `ims_sqtg_sun_ordergoods` `og` ON `og`.`order_id`=`o`.`id` 
LEFT JOIN `ims_sqtg_sun_goods` `g` ON `g`.`id`=`og`.`goods_id` 
LEFT JOIN `ims_sqtg_sun_category` `cat` ON `g`.`cat_id`=`cat`.`id` 
WHERE ( `o`.`uniacid` = 6 AND `o`.`uniacid` = 6 ) AND ( o.pay_state = 1 ) 
GROUP BY `g`.`batch_no`
ORDER BY `g`.`cat_id` DESC

3、sum 与count 组合使用

SELECT sum(heji) FROM  (
    SELECT  g.price * count(og.num) as heji   FROM `ims_sqtg_sun_order` `o` 
    LEFT JOIN `ims_sqtg_sun_ordergoods` `og` ON `og`.`order_id`=`o`.`id` 
    LEFT JOIN `ims_sqtg_sun_goods` `g` ON `g`.`id`=`og`.`goods_id` 
    LEFT JOIN `ims_sqtg_sun_supplier` `su` ON `su`.`sup_code`=`g`.`sup_code` 
    WHERE `o`.`uniacid` = 6 AND ( o.pay_state = 1 ) 

    GROUP BY g.id
    ORDER BY  g.id
)as ttt   /*嵌套select 必须使用as 别名*/

在tp5中 一般将 “小计”查出来  ，用php 计算数组中各值得和的方法可以得到“总计”  array_sum() 函数。

 $a=array(5,15,25);
 echo array_sum($a)

4、group by 多个字段的问题

insert into test(a,b,c) values('1','a','x');

insert into test (a,b,c) values('1','a','x');
insert into test(a,b,c) values('1','a','x');
insert into test(a,b,c) values('1','a','x');
insert into test(a,b,c) values('1','a','y');
insert into test(a,b,c) values('1','b','y');
insert into test(a,b,c) values('1','b','y');
insert into test(a,b,c) values('1','b','y');

5个a,3个b,4个x,4个y