oracle 日期列的 SQL 计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2375781/
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
SQL Count for a Date Column
提问by Sheldon
I have a table that containts a set of columns one of it is a Date column.
我有一个包含一组列的表，其中一个是日期列。
I need to count how many occurrences of the values of that column refer to the same month. And return if for one month, that count sums more than 3.
我需要计算该列的值引用同一个月的次数。如果一个月内，该计数的总和超过 3，则返回。
For example:
例如：
____________________
| DATE | .... |
---------------------
1998-09-02
1998-09-03
1998-10-03
1998-10-04
This must return no value. Because it doesn't have the necessary number of repetitions.
这必须不返回任何值。因为它没有必要的重复次数。
But this it does:
但这确实是：
____________________
| DATE | .... |
---------------------
1998-09-02
1998-09-03
1998-09-12
1998-09-14
1998-10-02
1998-11-21
For the november month.
对于 11 月。
Is for an Oracle DB.
用于 Oracle DB。
采纳答案by THEn
SELECT
COUNT(date)
, TRUNC(DATE,'MON')
FROM TABLE
GROUP BY TRUNC(DATE,'MON')
HAVING COUNT(DATE) > 3
回答by Gary Myers
create table x (date_col date);
insert into x values (date '1998-09-02');
insert into x values (date '1998-09-03');
insert into x values (date '1998-09-12');
insert into x values (date '1998-09-14');
insert into x values (date '1998-10-02');
insert into x values (date '1998-11-21');
SELECT TRUNC(date_col,'MM'), count(*)
FROM x
GROUP BY TRUNC(date_col,'MM')
HAVING count(*) > 3;
回答by Darren Atkinson
This example will help :
这个例子将有助于：
create table d1
( event_date date, event_description varchar2(100));
insert into d1 values (sysdate,'Phone Call');
insert into d1 values (sysdate,'Letter');
insert into d1 values (sysdate-50,'Interview');
insert into d1 values (sysdate-50,'Dinner with parents');
insert into d1 values (sysdate-100,'Birthday');
insert into d1 values (sysdate-100,'Holiday');
insert into d1 values (sysdate-100,'Interview');
insert into d1 values (sysdate-100,'Phone Call');
commit;
select * from d1;
EVENT_DATE EVENT_DESCRIPTION
------------------------- -----------------------------------------------
04-MAR-10 14.47.58 Phone Call
04-MAR-10 14.47.58 Letter
13-JAN-10 14.47.58 Interview
13-JAN-10 14.47.58 Dinner with parents
24-NOV-09 14.47.58 Birthday
24-NOV-09 14.47.58 Holiday
24-NOV-09 14.47.58 Interview
24-NOV-09 14.47.58 Phone Call
8 rows selected
You can see that Nov-09 is the only month which more than 3 events.
您可以看到 11 月 9 日是唯一一个超过 3 个事件的月份。
Referring back to your original question, which was And return if for one month, that count sums more than 3. The following SQL aggregate will work.
回到你最初的问题，如果一个月后返回，那么计数总和超过 3。以下 SQL 聚合将起作用。
select trunc(event_date,'MONTH'),count('x') from d1
having count('x') > 3 group by trunc(event_date,'MONTH')
Alternatively, use to_char to convert the Date type to a Char with a MON-YYYY picture as follows :
或者，使用 to_char 将 Date 类型转换为带有 MON-YYYY 图片的 Char，如下所示：
select to_char(trunc(event_date,'MONTH'),'MON-YYYY') month,
count('x') no_of_occurances from d1 having count('x') > 3 group trunc(event_date,'MONTH')
回答by bic
Ideally you should create a stored procedure that accepts the two criteria you need, Month(integer) and limit(integer)
理想情况下，您应该创建一个接受您需要的两个条件的存储过程，Month(integer) 和 limit(integer)
In a parameterized procedure that executes the following
在执行以下操作的参数化过程中
SELECT MONTH(Date) AS TheMonth, COUNT(MONTH(Date)) AS TheMonthCount
FROM MyTable
GROUP BY MONTH(Date)
HAVING (COUNT(MONTH(Date)) > @limit) AND (MONTH(Date) = @month)
To also output the relevant month you could use the following
要还输出相关月份，您可以使用以下内容
SELECT CAST(YEAR(Date) AS NVARCHAR) + '.' +
CAST(MONTH(Date) AS NVARCHAR) AS 'The ',
MONTH(Date ) AS TheMonth, COUNT(MONTH(Date)) AS TheMonthCount
FROM Audit_Entry
GROUP BY MONTH(Date),
CAST(YEAR(Date) AS NVARCHAR) + '.' +
CAST(MONTH(Date) AS NVARCHAR)
HAVING (COUNT(MONTH(Date)) > @limit) AND (MONTH(Date) = @month)
回答by edorian
So if 3 coloums contain 1999-01-xx you want to get that fetched ?
因此，如果 3 个 coloums 包含 1999-01-xx，您想获取它吗？
SELECT YEAR(date), MONTH(date)
FROM table GROUP BY YEAR(date), MONTH(date)
HAVING COUNT(*) > 3
If you need all the rows that contain the upper result it should look something like that
如果您需要包含较高结果的所有行，它应该看起来像这样
SELECT * FROM table
INNER JOIN (
SELECT YEAR(date) as y, MONTH(date) as m
FROM table GROUP BY YEAR(date), MONTH(date)
HAVING COUNT(*) > 3
) as virtualTable
ON virtualTable.y = YEAR(date) AND virtualTable.m = MONTH(date)
回答by Harish Shetty
回答by Olivier Croisier
You could use Oracle's EXTRACT method :
您可以使用 Oracle 的 EXTRACT 方法：
select theMonth, sum(monthCount)
from (
select
extract(MONTH FROM t.theDateColumn) as theMonth,
1 as monthCount
)
group by theMonth
having sum(monthCount) >= 3
I don't have an Oracle database at hand at the moment, so this code may not work as is - I apologize for this.
我目前手头没有 Oracle 数据库，因此此代码可能无法按原样运行 - 对此我深表歉意。
回答by AxelEckenberger
This should work for mysql and mssql:
这应该适用于 mysql 和 mssql：
SELECT MONTH(date), Sum(MONTH(date))
FROM table
GROUP BY date
HAVING Sum(MONTH(date)) > 3
回答by JL.
Could be wrong but a guess:
可能是错误的，但猜测：
SELECT SUM(date) FROM table
GROUP BY date where SUM(date) > 3
