㈠ 求oracle按天查詢sql語句
selectto_char(date,'yyyy-mm-dd')date,count(1)fromtablewheredate>to_date('2015-08-01','yyyy-mm-dd')anddate<to_date('2015-08-31','yyyy-mm-dd')groupbydateorderbydateasc
沒有數據顯示0這個不太好辦，臨時還沒想到
㈡ Oracle資料庫按時間進行分組統計數據的方法
Oracle按不同時間分組統計的sql
如下表table1：
日期(exportDate)
數量(amount)
--------------
-----------
14-2月
-08
20
10-3月
-08
2
14-4月
-08
6
14-6月
-08
75
24-10月-09
23
14-11月-09
45
04-8月
-10
5
04-9月
-10
44
04-10月-10
88
注意：為了顯示更直觀，如下查詢已皆按相應分組排序
1.按年份分組
select
to_char(exportDate,'yyyy'),sum(amount)
from
table1
group
by
to_char(exportDate,'yyyy');
年份
數量
-----------------------------
2009
68
2010
137
2008
103
2.按月份分組
select
to_char(exportDate,'yyyy-mm'),sum(amount)
from
table1
group
by
to_char(exportDate,'yyyy-mm')
order
by
to_char(exportDate,'yyyy-mm');
月份
數量
-----------------------------
2008-02
20
2008-03
2
2008-04
6
2008-06
75
2009-10
23
2009-11
45
2010-08
5
2010-09
44
2010-10
88
3.按季度分組
select
to_char(exportDate,'yyyy-Q'),sum(amount)
from
table1
group
by
to_char(exportDate,'yyyy-Q')
order
by
to_char(exportDate,'yyyy-Q');
季度
數量
------------------------------
2008-1
22
2008-2
81
2009-4
68
2010-3
49
2010-4
88
4.按周分組
select
to_char(exportDate,'yyyy-IW'),sum(amount)
from
table1
group
by
to_char(exportDate,'yyyy-IW')
order
by
to_char(exportDate,'yyyy-IW');
周
數量
------------------------------
2008-07
20
2008-11
2
2008-16
6
2008-24
75
2009-43
23
2009-46
45
2010-31
5
2010-35
44
2010-40
88
PS：Oracle按時間段分組統計
想要按時間段分組查詢，首先要了解level,connect
by,oracle時間的加減.
關於level這里不多說，我只寫出一個查詢語句:
----level
是一個偽例
select
level
from
al
connect
by
level
<=10
---結果：
1
2
3
4
5
6
7
8
9
10
oracle時間的加減看看試一下以下sql語句就會知道:
select
sysdate
-1
from
al
----結果減一天,也就24小時
select
sysdate-(1/2)
from
al
-----結果減去半天，也就12小時
select
sysdate-(1/24)
from
al
-----結果減去1
小時
select
sysdate-((1/24)/12)
from
al
----結果減去5分鍾
select
sydate-(level-1)
from
al
connect
by
level<=10
---結果是10間隔1天的時間
下面是本次例子：
select
dt,
count(satisfy_degree)
as
num
from
T_DEMO
i
,
(select
sysdate
-
(level-1)
*
2
dt
from
al
connect
by
level
<=
10)
d
where
i.satisfy_degree='satisfy_1'
and
i.insert_time<dt
and
i.insert_time>
d.dt-2
group
by
d.dt
例子中的sysdate
-
(level-1)
*
2得到的是一個間隔是2天的時間
group
by
d.dt
也就是兩天的時間間隔分組查詢
自己實現例子：
create
table
A_HY_LOCATE1
(
MOBILE_NO
VARCHAR2(32),
LOCATE_TYPE
NUMBER(4),
AREA_NO
VARCHAR2(32),
CREATED_TIME
DATE,
AREA_NAME
VARCHAR2(512),
)；
select
(sysdate-13)-(level-1)/4
from
al
connect
by
level<=34
--從第一條時間記錄開始（sysdate-13）為表中的最早的日期，「34」出現的分組數（一天按每六個小時分組
就應該為4）
一下是按照每6個小時分組
select
mobile_no,area_name,max(created_time
),dt,
count(*)
as
num
from
a_hy_locate1
i
,
(select
(sysdate-13)-(level-1)/4
dt
from
al
connect
by
level
<=
34)
d
where
i.locate_type
=
1
and
i.created_time<dt
and
i.created_time>
d.dt-1/4
group
by
mobile_no,area_name,d.dt
另外一個方法：
--按六小時分組
select
trunc(to_number(to_char(created_time,
'hh24'))
/
6),count(*)
from
t_test
where
created_time
>
trunc(sysdate
-
40)
group
by
trunc(to_number(to_char(created_time,
'hh24'))
/
6)
--按12小時分組
select
trunc(to_number(to_char(created_time,
'hh24'))
/
6),count(*)
from
t_test
where
created_time
>
trunc(sysdate
-
40)
group
by
trunc(to_number(to_char(created_time,
'hh24'))
/
6)
㈢ oracle 查詢一段時間內每一天的統計數據sql怎麼寫
還有這么到的？？？2015-09-01到2014-09-12
select date,count(*) as number from table_name where date = '2015-09-01'
union
select date,count(*) as number from table_name where date = '2015-09-02'
如果天數比較少 可以這么干，如果比較多可以寫存儲過程，循環搞
㈣ oracle資料庫 date時間類型查詢昨天、前7天、前30天、前1年的數據，sql語句怎麼寫
1、打開plsql，連接上oracle資料庫，使用sql語句創建一張測試表。
㈤ oracle統計查詢 sql語句應該怎麼寫
select substrb(create_time,1,4) "年份",
sum(decode(substrb(create_time,6,2),'01',commission,0)) "1月",
sum(decode(substrb(create_time,6,2),'02',commission,0)) "2月",
sum(decode(substrb(create_time,6,2),'03',commission,0)) "3月",
sum(decode(substrb(create_time,6,2),'04',commission,0)) "4月",
sum(decode(substrb(create_time,6,2),'05',commission,0)) "5月",
sum(decode(substrb(create_time,6,2),'06',commission,0)) "6月",
sum(decode(substrb(create_time,6,2),'07',commission,0)) "7月",
sum(decode(substrb(create_time,6,2),'08',commission,0)) "8月",
sum(decode(substrb(create_time,6,2),'09',commission,0)) "9月",
sum(decode(substrb(create_time,6,2),'10',commission,0)) "10月",
sum(decode(substrb(create_time,6,2),'11',commission,0)) "11月",
sum(decode(substrb(create_time,6,2),'12',commission,0)) "12月"
from test
group by substrb(create_time,1,4)
此語句是按create_time欄位是字元型給出的，如果你的表中此欄位是日期型，則進行一下轉化
㈥ oracle統計查詢 sql語句應該怎麼寫
select
substrb(create_time,1,4)
"年份",
sum(decode(substrb(create_time,6,2),'01',commission,0))
"1月",
sum(decode(substrb(create_time,6,2),'02',commission,0))
"2月",
sum(decode(substrb(create_time,6,2),'03',commission,0))
"3月",
sum(decode(substrb(create_time,6,2),'04',commission,0))
"4月",
sum(decode(substrb(create_time,6,2),'05',commission,0))
"5月",
sum(decode(substrb(create_time,6,2),'06',commission,0))
"6月",
sum(decode(substrb(create_time,6,2),'07',commission,0))
"7月",
sum(decode(substrb(create_time,6,2),'08',commission,0))
"8月",
sum(decode(substrb(create_time,6,2),'09',commission,0))
"9月",
sum(decode(substrb(create_time,6,2),'10',commission,0))
"10月",
sum(decode(substrb(create_time,6,2),'11',commission,0))
"11月",
sum(decode(substrb(create_time,6,2),'12',commission,0))
"12月"
from
test
group
by
substrb(create_time,1,4)
此語句是按create_time欄位是字元型給出的，如果你的表中此欄位是日期型，則進行一下轉化
㈦ oracle 用SQL查詢一個時間段每天的數據量
按日做匯總啊：
selecttrunc(datecol)日期,sum(數量)數量和,count(1)數據量
fromtablex
whereto_char(datecol,'yyyymm')='201305'
groupbytrunc(datecol);
㈧ oracle資料庫中如何實現數據的跨天分時段統計
select
to_char(日期,'yyyy-mm-dd'),
count(1)
from
tab
where
to_char(日期,'hh24miss') between '200000' and '235959'
or
to_char(日期,'hh24miss') between '000000' and '080000'
group by to_char(日期,'yyyy-mm-dd');
㈨ oracle 查詢一段時間內每一天的統計數據sql怎麼寫
可以寫一個簡單的procere來實現,原理是遍歷日期范圍,並查詢日期資料筆數,寫入表。
數據源表test03
1 2016-06-01 1
2 2016-06-02 1
3 2016-06-05 1
4 2016-06-04 1
5 2016-06-04 1
procere代碼如下:
create or replace procere loop_by_date(pbeg_tim in varchar2,--開始日期
pend_tim in varchar2,--結束日期
errmessage out varchar2) is
nCount number(10); --總天數
i_point number(10); --當天
is_zero number(10); --當天是否有記錄
begin
nCount := 0;
i_point := 0;
is_zero := 0;
select ROUND(to_date(pend_tim, 'yyyy-mm-dd') -
to_date(pbeg_tim, 'yyyy-mm-dd'))
into nCount
from al;
delete from test02;
<<fst_loop>>
loop
select count(*)
into is_zero
from test03
where date1 =
to_char(to_date(pbeg_tim, 'yyyy-mm-dd') + i_point, 'yyyy-mm-dd');
insert into test02
(date01, nccount)
values
(to_char(to_date(pbeg_tim, 'yyyy-mm-dd') + i_point, 'yyyy-mm-dd'),
is_zero);
i_point := i_point + 1;
exit fst_loop when i_point >= nCount;
end loop fst_loop;
--end;
end loop_by_date;
傳入參數"2016-06-01"~~"2016-06-10"並執行,結果寫入test02為:
1 2016-06-01 1
2 2016-06-02 1
3 2016-06-03 0
4 2016-06-04 2
5 2016-06-05 1
6 2016-06-06 0
7 2016-06-07 0
8 2016-06-08 0
9 2016-06-09 0
㈩ oracle日結統計，求大神幫忙分析一下統計SQL如何書寫
表應該加上注釋，要不沒法寫。
大概思路是這樣的，首先統計出每一天每一個員工最晚一次的日結時間。這里分為兩種情況，周5和其他日期需要分別判斷。
然後將用戶表和剛才的日結匯總表進行左連接，日結匯總表為空的部分就意味著該員工沒有日結，外面套一層，count一下即可。