Oracle SQL 查询计数分组按时间戳子串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3527066/
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 query count group by timestamp substring
提问by krick
Given a table that has a column of string "timestamps" (yyyyMMddHHmmssSSS format), I want to substring the first 8 characters, and get a count of how many rows have that substring, grouping the results.
给定一个包含一列字符串“时间戳”（yyyyMMddHHmmssSSS 格式）的表，我想对前 8 个字符进行子字符串化，并计算该子字符串有多少行，对结果进行分组。
Sample data...
样本数据...
TIMESTAMP
20100802123456123
20100803123456123
20100803123456123
20100803123456123
20100804123456123
20100805123456123
20100805123456123
20100805123456123
20100805123456123
20100806123456123
20100807123456123
20100807123456123
...and expected results...
......以及预期的结果......
SUBSTRING, COUNT
20100802, 1
20100803, 3
20100804, 1
20100805, 4
20100806, 1
20100807, 2
I know this should be easy, but I'm not having any luck at the moment.
我知道这应该很容易，但我目前没有任何运气。
回答by Brandon Horsley
I don't have a database to test with, but it seems like you are looking for
我没有要测试的数据库，但您似乎正在寻找
select
substr(timestamp, 1, 8),
count(*)
from
my_table
group by
substr(timestamp, 1, 8);
