使用按日期分组的 sql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20171768/
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
using sql query with group by date
提问by user3026655
I'm trying to write this query to retrieve No. of records grouped by date:
我正在尝试编写此查询以检索按日期分组的记录数：
SELECT system_date,count (is_paid)
FROM TPWEB.TP_CLIENT_TENDER_TRANS
where system_date between '1-DEC-12' and '31-DEC-12'
group by system_date
But, I got result without grouping such as:
但是，我得到了没有分组的结果，例如：
01-DEC-12 1
01-DEC-12 1
01-DEC-12 1
01-DEC-12 1
what is wrong...
怎么了...
回答by Ben
As is documentedthe DATE datatype is a datetime and thus stores the hour, minute and second in addition to the attributes you'd, more reasonably expect, in a datatype with this name. If you want to count over a day you need to remove the time portion of your date. It is the default behaviour of TRUNC(), so to do this trunc(<date>)is all you need.
正如所记录的那样，DATE 数据类型是一个日期时间，因此除了您更合理地期望的属性之外，还存储小时、分钟和秒，并以具有此名称的数据类型存储。如果您想计算一天，您需要删除日期的时间部分。这是 的默认行为TRUNC()，因此trunc(<date>)您只需要执行此操作。
It's worth noting two things at this point:
在这一点上有两点值得注意：
I'm assuming
system_dateis a column in your table and not a misunderstanding of SYSDATEYour between clause is completely incorrect, dangerously so.
By the way your dates have been represented it appears as though your NLS_DATE_FORMAT is DD-MON-YYYY (see another answer of minefor more details). This means that when you implicitly convert a date into a character it is converted in this format.
You're not using either a datetime literalor an explicit conversionof the values you're comparing to, which means your date is being implicitly converted to a character. However, when you do the comparison you'll find that things aren't always as they seem. Character comparison is, normally, binary; this means that the 10thof February is not between the 10thJanuary and the 10thMarch; "March" is smaller than "January".
Alwaysexplicitly convert dates and alwaysuse dates when doing date comparisons.
我假设
system_date是您表中的一列，而不是对SYSDATE的误解您的 between 子句完全不正确，非常危险。
通过表示您的日期的方式，您的 NLS_DATE_FORMAT 似乎是 DD-MON-YYYY（有关更多详细信息，请参阅我的另一个答案）。这意味着当您将日期隐式转换为字符时，它会以这种格式转换。
您没有使用日期时间文字或您要比较的值的显式转换，这意味着您的日期被隐式转换为字符。但是，当您进行比较时，您会发现事情并不总是像看起来的那样。字符比较通常是二进制的；这意味着2 月10日不在1 月10日和3 月10日之间；“三月”比“一月”小。
在进行日期比较时，始终显式转换日期并始终使用日期。
Putting all of this together your query becomes:
将所有这些放在一起，您的查询变为：
select trunc(system_date), count(is_paid)`
from TPWEB.TP_CLIENT_TENDER_TRANS
where system_date between date '2012-12-01' and date '2012-12-31'
group by trunc(system_date)
回答by Mureinik
DATEcolumns in Oracle contain precision up to milliseconds, not just the date. If you're only interested in the date itself, you should TRUNCit to remove hours, minutes, etc.:
DATEOracle 中的列包含精度高达毫秒，而不仅仅是日期。如果您只对日期本身感兴趣，则应该TRUNC删除小时、分钟等：
SELECT system_date,COUNT (is_paid)
FROM TPWEB.TP_CLIENT_TENDER_TRANS
WHERE system_date BETWEEN '1-DEC-12' AND '31-DEC-12'
GROUP BY TRUNC(system_date, 'DD-MON-YY')
回答by Ashutosh Ranjan
Use trunc function, the DATE datatype holds the time with it, so using trunc we can truncate time. Below query works!
使用 trunc 函数，DATE 数据类型用它保存时间，所以使用 trunc 我们可以截断时间。下面的查询有效！
SELECT trunc(system_date),count (is_paid)
FROM TPWEB.TP_CLIENT_TENDER_TRANS
where system_date between '1-DEC-12' and '31-DEC-12'
group by trunc(system_date);
回答by julio
This works for me:
这对我有用：
select DT_FECHA_DESDE,DT_FECHA_HASTA
from (SELECT TRUNC(DT_FECHA_DESDE)as DT_FECHA_DESDE,TRUNC(DT_FECHA_HASTA) as DT_FECHA_HASTA
FROM TBL1
WHERE run='3456'
order by DT_FECHA_DESDE)
group by DT_FECHA_DESDE,DT_FECHA_HASTA
