Shulou(Shulou.com)06/01 Report--

Oracle sorts queries by day, week, month, quarter, and year

Days-- to_char (t.startkeeper timestamp / yyyyyqdd`) week-- to_char (t.startcalendar timestamp yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

Query by day

Select to_char (t.startkeeper timekeeper / YYYYmurMMMurDD') day, count (*) from test t where to_char (t.startroomtimememe / YYYYY') = '2019'-conditional restriction group by to_char (t.startroomtime.')-grouping order by to_char (t.startroomtime.'

Query by week

Select to_char (t.startroomtimereportyyyyyyy`) year,to_char (t.startroomtimetimereportyyyyyy`), count (*) from test t where to_char (t.startroomtimepensionyyyyyy`) = '2019'-conditional restrictions group by to_char (t.startroomtimetime.') year,to_char (t.startroomtime.') year,to_char (t.start_time)-- grouping 'IW')-- sort

Query by month

Select to_char (t. Startkeeper timebook YYYMurMM'), count (*) from test t where to_char (t.startkeeper timestamp YYYYYY') = '2019'-conditional restriction group byto_char (t.startroomtime.')-grouping order byto_char (t.startroomtimetime.')-sorting

Query by quarter

Select to_char (t. Startkeeper timejournal YYYY') year, to_char (t. Startkeeper timetag YYYY'), count (*) from test t where to_char (t.startroomtimejigree YYYY') = '2019'-conditional restrictions group byto_char (t.startkeeper timetime` YYY'), to_char (t.startshiftimejiggery YYY')-- grouping order byto_char (t.startkeeper timeframe YYYY'), to_char (t.start_time) 'Q')-sort

Query by year

Select to_char (t. Startkeeper timejournal YYYY') year, count (*) from test t where to_char (t. Startkeeper timedepartment YYYY') = '2019'-conditional restriction group by to_char (t.startkeeper timestamp YYYY')-grouping order by to_char (t.startkeeper timestamp YYYY')-sorting

Knowledge point expansion: oracle can query statistics by day, week, month, quarter and year.

Here is a good way to query statistics on a weekly, monthly, quarterly and annual basis by oracle.

Also saw on the Internet to do with trunc, the following is an example, two SQL effect is the same.

Id has duplicates, so group by has two fields.

Only tried in the Oracle database, other libraries have not tried.

Create table CONSUMER_ACC (ID VARCHAR2 (50) not null, ACC_NUM VARCHAR2 (10), DATETIME DATE) select t.idschold trunc (t.datetime, 'mm') as d, sum (t.acc_num) as n from CONSUMER_ACC t-- where group by t.idrecoery trunc (t.datetime,' mm') order by n desc Select t.idjinger toddchar (t.datetime, 'mm') d, sum (t.acc_num) n from CONSUMER_ACC t-- where group by t.idjingle toddchar (t.datetime) 'mm') order by n desc-daily statistics of select count (dataid) as daily operations, sum () from where group by trunc (createtime) 'DD')) / / Statistics by natural week select to_char (date,'iw'), sum () from where group by to_char (date,'iw') / / Statistics by natural month select to_char (date,'mm'), sum () from where group by to_char (date,'mm') / / Quarterly statistics select to_char (date,'q'), sum () from where group by to_char (date) 'q') / / Annual statistics select to_char (date,'yyyy'), sum () from where group by to_char (date,'yyyy')

Summary

The above is the editor to introduce to you the oracle implementation by day, week, month, quarter, annual query sorting method, I hope to help you, if you have any questions, please leave me a message, the editor will reply to you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.