是否可以根据日期或顺序记录mysql中有直线行的数据？
是否可以根据日期或顺序记录mysql中有直线行的数据？
提问于 2014-08-25 04:18:20
我想得到的数据，有一个直销超过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 |
+----------+---------------------+--------+-------------+回答 1
Stack Overflow用户
回答已采纳
发布于 2014-08-25 04:25:34
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工作演示
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/25478977
复制相关文章
相似问题
腾讯云开发者
