56,687
社区成员
发帖
与我相关
我的任务
分享
select corpid,订单日期 as 首单日期,金额 as 首单金额
from (
select corpid,date(createtime) as 订单日期,sum(orderamount) as 金额
from orders as A
group by corpid,date(createtime)
order by corpid,date(createtime)
) as b # b表根据企业客户 订单日期group by 同时根据这两个字段升序 形成一个子表 然后从子表只依据企业客户group by 取首单
group by
corpid
select corpid,金额 as 首单金额
from (
select corpid,sum(orderamount) as 金额
from orders as A
group by corpid,date(createtime)
order by corpid,date(createtime)
) as b # b表根据企业客户 订单日期group by 同时根据这两个字段升序 形成一个子表 然后从子表只依据企业客户group by 取首单
group by corpid
+--------------+----------+------------------+----------+------------------+
| departmentid | count(*) | sum(orderamount) | count(*) | sum(orderamount) |
+--------------+----------+------------------+----------+------------------+
| 2 | 2 | 458.00 | 2 | 458.00 |
| 3 | 1 | 200.00 | 2 | 242.00 |
| 6 | 1 | 100.00 | 2 | 200.00 |
+--------------+----------+------------------+----------+------------------+
3 rows in set (0.00 sec)
mysql> select * from tmpOrders;
+---------+--------------+--------+-------------+---------------------+
| orderid | departmentid | corpid | orderamount | createtime |
+---------+--------------+--------+-------------+---------------------+
| 12 | 3 | 999 | 100.00 | 2016-03-10 12:53:23 |
| 23 | 2 | 444 | 158.00 | 2016-06-07 20:42:23 |
| 43 | 3 | 777 | 200.00 | 2016-06-14 18:20:45 |
| 78 | 6 | 555 | 100.00 | 2016-06-23 15:56:23 |
| 34 | 3 | 333 | 42.00 | 2016-06-10 12:20:23 |
| 22 | 2 | 111 | 300.00 | 2016-06-08 14:20:23 |
| 13 | 6 | 444 | 100.00 | 2016-06-12 09:11:33 |
+---------+--------------+--------+-------------+---------------------+
7 rows in set (0.00 sec)
mysql> select departmentid,count(*),sum(orderamount),count(*),sum(orderamount)
-> from tmpOrders t
-> where createtime between '2016-06-01' and '2016-06-30 23:59:59'
-> and not exists (
-> select 1 from
-> tmpOrders
-> where createtime >= '2016-06-01'
-> and corpid=t.corpid
-> and createtime<t.createtime)
-> group by departmentid;
+--------------+----------+------------------+----------+------------------+
| departmentid | count(*) | sum(orderamount) | count(*) | sum(orderamount) |
+--------------+----------+------------------+----------+------------------+
| 2 | 2 | 458.00 | 2 | 458.00 |
| 3 | 2 | 242.00 | 2 | 242.00 |
| 6 | 1 | 100.00 | 1 | 100.00 |
+--------------+----------+------------------+----------+------------------+
3 rows in set (0.00 sec)
mysql>