Oracle/SQL - 按天按时间对项目进行分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6067083/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle/SQL - Grouping items by action by day over time
提问by dscl
Hi all I have a 'widget' table that has the following columns: widget, action, timestamp_. What we want to do is pull all the widgets that were rejected more than once a day between certain dates. So here's an example table
大家好，我有一个“小部件”表，其中包含以下列：小部件、操作、时间戳_。我们想要做的是在特定日期之间每天拉出被拒绝多次的所有小部件。所以这是一个示例表
widget action timestamp_
-------------------------------------------
type1 reject 2011-05-10 08:00:00
type1 reject 2011-05-10 09:00:00
type1 reject 2011-05-10 09:30:00
type2 reject 2011-05-11 09:30:00
type3 reject 2011-05-11 09:30:00
type1 reject 2011-05-11 09:30:00
type1 reject 2011-05-11 09:30:00
type2 reject 2011-05-12 10:30:00
type2 reject 2011-05-12 12:30:00
type3 reject 2011-05-12 12:30:00
So I anticipate wanting to see results in one of these two manners....
所以我希望以这两种方式之一看到结果......
Between date x and y there were two widgets that were rejected multiple times in single days
在日期 x 和 y 之间，有两个小部件在一天内被多次拒绝
This would see that type1 was rejected more than once in a day as was type2 thus the count is: 2
这将看到 type1 和 type2 一样在一天内被拒绝多次，因此计数为：2
OR
或者
Display each widget along with the date that it was rejected more than once and how many times. Example..
显示每个小部件以及它被多次拒绝的日期和次数。例子..
widget date count
---------------------------------
type1 2011-05-10 3
type1 2011-05-11 2
type2 2011-05-12 2
This would probably be the preferred output... but how?
这可能是首选的输出......但是如何？
Thanks in advance!
提前致谢！
回答by MPelletier
This would give your output:
这会给你的输出：
SELECT Widget, to_char(timestamp_,'YYYY-MM-DD'), Count(Widget)
FROM Widget
WHERE timestamp_ BETWEEN to_date('YYYY-MM-DD HH24:MI:SS','%date1%') AND to_date('YYYY-MM-DD HH24:MI:SS','%date2%')
AND action LIKE 'reject'
GROUP BY Widget, to_char(timestamp_,'YYYY-MM-DD')
HAVING Count(Widget) > 1;
Of course, you'll want to replace the date variables.
当然，您需要替换日期变量。
回答by Sanjay
Try the below one and you will get what you want :-
试试下面的一个，你会得到你想要的：-
Select widget,convert(varchar,[timestp],102) As Date,COUNT(timestp) as Count
From MTest
group by widget,[action],convert(varchar,[timestp],102)
having COUNT(timestp)>1
