我想获得直接销售超过3次的数据,这是可能的mysql?如果不是,怎样才能做到呢?我需要它对mysql或php。
my database:-
+----------+---------------------+--------+
| Username | Date | Action |
+----------+---------------------+--------+
| Adam | 2014-08-20 22:30:20 | Sell |
| Adam | 2014-08-20 22:30:20 | Sell |
| Adam | 2014-08-20 22:30:20 | Sell |
| Adam | 2014-08-20 22:30:20 | Buy |
| Adam | 2014-08-20 22:30:20 | Buy |
| Adam | 2014-08-20 22:30:20 | Sell |
| Adam | 2014-08-20 22:30:20 | Sell |
| Adam | 2014-08-20 22:30:20 | Sell |
| Adam | 2014-08-20 22:30:20 | Sell |
| Nick | 2014-08-20 22:30:20 | Sell |
| Nick | 2014-08-20 22:30:20 | Sell |
| Nick | 2014-08-20 22:30:20 | Sell |
| Nick | 2014-08-20 22:30:20 | Sell |
| Nick | 2014-08-20 22:30:20 | Buy |
+----------+---------------------+--------+
从上表中,我需要列出所有直接销售超过3次的数据。
RESULT
+----------+---------------------+--------+-------------+
| Username | Date | Action | Straight 3+ |
+----------+---------------------+--------+-------------+
| Adam | 2014-08-20 22:30:20 | Sell | 3 |
| Adam | 2014-08-20 22:30:20 | Sell | 4 |
| Nick | 2014-08-20 22:30:20 | Sell | 4 |
+----------+---------------------+--------+-------------+
SELECT username, date, action, num_times as 'Straight 3+'
FROM
( SELECT *,
SUM(case action WHEN 'Sell' THEN 1 ELSE 0 END) as num_times,
if(@a = action, @b, @b:= @b + 1) as counting_col,
@a := action
FROM your_table
CROSS JOIN (SELECT @a := '', @b := 1) t
GROUP BY username, counting_col
HAVING num_times > 0
) t1
演示工作