56,675
社区成员
发帖
与我相关
我的任务
分享
mysql> set @start_time := unix_timestamp("2012-03-01");
Query OK, 0 rows affected (0.00 sec)
mysql> set @end_time := unix_timestamp("2012-03-31");
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select date, between_stat.user_id, between_stat.total - ifnull(before_sta
t.total, 0) as '重复次数', between_stat.total as '总共次数', between_stat.total_
cost - ifnull(before_stat.total_cost, 0) as '重复购买金额'
-> from
-> (
-> select
-> concat(date_format(from_unixtime(min(cdate)), "%Y-%m-%d"), "至",
date_format(from_unixtime(max(cdate)), "%Y-%m-%d")) as date,
-> user_id,
-> count(user_id) as total,
-> sum(order_total) as total_cost
-> from orders
-> where cdate between @start_time and @end_time
-> group by user_id
-> ) as between_stat
->
-> left join
->
-> (
-> select
-> user_id,
-> count(user_id) as total,
-> sum(order_total) as total_cost
-> from orders
-> where cdate < @start_time
-> group by user_id
-> ) as before_stat
->
-> on between_stat.user_id = before_stat.user_id;
+------------------------+---------+----------+----------+--------------+
| date | user_id | 重复次数 | 总共次数 | 重复购买金额 |
+------------------------+---------+----------+----------+--------------+
| 2012-03-27至2012-03-27 | 1 | 2 | 2 | 105.99000 |
| 2012-03-27至2012-03-27 | 3 | 3 | 3 | 89.00000 |
| 2012-03-27至2012-03-27 | 5 | 1 | 1 | 36.00000 |
| 2012-03-27至2012-03-27 | 64 | 2 | 2 | 231.03000 |
+------------------------+---------+----------+----------+--------------+
4 rows in set (0.00 sec)
mysql>
mysql> select
-> concat(date_format(from_unixtime(min(cdate)), "%Y-%m-%d"), "至", date
_format(from_unixtime(max(cdate)), "%Y-%m-%d")) as data,
-> user_id,
-> count(user_id) - 1 as '重复次数',
-> count(user_id) as '总数',
-> sum(order_total) as '重复购买金额'
-> from orders
-> group by user_id
-> having count(user_id) > 1;
+------------------------+---------+----------+------+--------------+
| data | user_id | 重复次数 | 总数 | 重复购买金额 |
+------------------------+---------+----------+------+--------------+
| 2012-03-27至2012-03-27 | 1 | 1 | 2 | 105.99000 |
| 2012-03-27至2012-03-27 | 3 | 2 | 3 | 89.00000 |
| 2012-03-27至2012-03-27 | 64 | 1 | 2 | 231.03000 |
+------------------------+---------+----------+------+--------------+
3 rows in set (0.00 sec)
+------------------------+------+--------------+------+
| date | 重复次数 | 总数 | 重复购买金额 |
+------------------------+------+--------------+------+
| 2012-03-27至2012-03-27 | 4 | 7 | 426.02000 |
+------------------------+------+--------------+------+