我测试是成功的,你试试看~~~ select * from (select count(a.a1) as t_1 from A a where a.t1 >= to_date(to_char(sysdate, 'yyyy') || '01', 'yyyy-mm') and a.t1 <= to_date(to_char(add_months(sysdate, -1), 'yyyy-mm'), 'yyyy-mm')), (select count(a.a1) as t_2 from A a where to_char(a.t1, 'yyyymm') = to_char(sysdate, 'yyyymm')), (select count(a.a1) as t_3 from A a where a.t1 >= sysdate - (to_char(sysdate - 1, 'D') - 1) and a.t1 <= sysdate + (7 - to_char(sysdate - 1, 'D'))), (select count(a.a1) as t_4 from A a where to_char(a.t1, 'yyyy') = to_char(sysdate, 'yyyy'));
where a.t1 >= to_date(to_char(sysdate, 'yyyy') || '01', 'yyyy-mm') and a.t1 <= to_date(to_char(add_months(sysdate, -1), 'yyyy-mm'), 'yyyy-mm')),-----------这里提示文字与格式字符串不匹配怎么办?
a.ti是date型的吗?select * from (select count(a.a1) as t_1 from A a where a.t1 >= to_date(to_char(sysdate, 'yyyy') || '01', 'yyyymm') and a.t1 <= to_date(to_char(add_months(sysdate, -1), 'yyyymm'), 'yyyymm')), (select count(a.a1) as t_2 from A a where to_char(a.t1, 'yyyymm') = to_char(sysdate, 'yyyymm')), (select count(a.a1) as t_3 from A a where a.t1 >= sysdate - (to_char(sysdate - 1, 'D') - 1) and a.t1 <= sysdate + (7 - to_char(sysdate - 1, 'D'))), (select count(a.a1) as t_4 from A a where to_char(a.t1, 'yyyy') = to_char(sysdate, 'yyyy'));
this is sql result which used test DB ... so i think it is right. SQL> select * 2 from (select count(*) as t_1 3 from AP_INVOICES a 4 where a.update_date >= to_date(to_char(sysdate, 'yyyy') || '01', 'yyyymm') 5 and a.update_date <= to_date(to_char(add_months(sysdate, -1), 'yyyymm'), 6 'yyyymm')), 7 (select count(*) as t_2 8 from AP_INVOICES a 9 where to_char(a.update_date, 'yyyymm') = to_char(sysdate, 'yyyymm')), 10 (select count(*) as t_3 11 from AP_INVOICES a 12 where a.update_date >= sysdate - (to_char(sysdate - 1, 'D') - 1) 13 and a.update_date <= sysdate + (7 - to_char(sysdate - 1, 'D'))), 14 (select count(*) as t_4 15 from AP_INVOICES a 16 where to_char(a.update_date, 'yyyy') = to_char(sysdate, 'yyyy')); ======================sql============================ T_1 T_2 T_3 T_4 ---------- ---------- ---------- ---------- 0 1047 1043 11788
好了 我看错了 , 我想知道为啥这样取出来的会比我以前 and a.t1 > = to_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and a.t1 < = to_date('2007-07-31 23:59:59','yyyy-mm-dd hh24:mi:ss') 取出来的数据会少
1 NULL 4
2 5 NULL
3 2 NULL
3 NULL 3
想要得结果:
1 NULL 4
2 5 NULL
3 2 3=======================================这样好理解你的需求一些 :-)
我以前都是这样写 t1> = to_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
select *
from (select count(a.a1) as t_1
from A a
where a.t1 >= to_date(to_char(sysdate, 'yyyy') || '01', 'yyyy-mm')
and a.t1 <= to_date(to_char(add_months(sysdate, -1), 'yyyy-mm'),
'yyyy-mm')),
(select count(a.a1) as t_2
from A a
where to_char(a.t1, 'yyyymm') = to_char(sysdate, 'yyyymm')),
(select count(a.a1) as t_3
from A a
where a.t1 >= sysdate - (to_char(sysdate - 1, 'D') - 1)
and a.t1 <= sysdate + (7 - to_char(sysdate - 1, 'D'))),
(select count(a.a1) as t_4
from A a
where to_char(a.t1, 'yyyy') = to_char(sysdate, 'yyyy'));
and a.t1 <= to_date(to_char(add_months(sysdate, -1), 'yyyy-mm'),
'yyyy-mm')),-----------这里提示文字与格式字符串不匹配怎么办?
to_char(a.t1, 'yyyymm') = to_char(sysdate, 'yyyymm')),
from (select count(a.a1) as t_1
from A a
where a.t1 >= to_date(to_char(sysdate, 'yyyy') || '01', 'yyyymm')
and a.t1 <= to_date(to_char(add_months(sysdate, -1), 'yyyymm'),
'yyyymm')),
(select count(a.a1) as t_2
from A a
where to_char(a.t1, 'yyyymm') = to_char(sysdate, 'yyyymm')),
(select count(a.a1) as t_3
from A a
where a.t1 >= sysdate - (to_char(sysdate - 1, 'D') - 1)
and a.t1 <= sysdate + (7 - to_char(sysdate - 1, 'D'))),
(select count(a.a1) as t_4
from A a
where to_char(a.t1, 'yyyy') = to_char(sysdate, 'yyyy'));
SQL> select *
2 from (select count(*) as t_1
3 from AP_INVOICES a
4 where a.update_date >= to_date(to_char(sysdate, 'yyyy') || '01', 'yyyymm')
5 and a.update_date <= to_date(to_char(add_months(sysdate, -1), 'yyyymm'),
6 'yyyymm')),
7 (select count(*) as t_2
8 from AP_INVOICES a
9 where to_char(a.update_date, 'yyyymm') = to_char(sysdate, 'yyyymm')),
10 (select count(*) as t_3
11 from AP_INVOICES a
12 where a.update_date >= sysdate - (to_char(sysdate - 1, 'D') - 1)
13 and a.update_date <= sysdate + (7 - to_char(sysdate - 1, 'D'))),
14 (select count(*) as t_4
15 from AP_INVOICES a
16 where to_char(a.update_date, 'yyyy') = to_char(sysdate, 'yyyy'));
======================sql============================ T_1 T_2 T_3 T_4
---------- ---------- ---------- ----------
0 1047 1043 11788
and a.t1 > = to_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.t1 < = to_date('2007-07-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
取出来的数据会少
and a.t1 <= sysdate + (7 - to_char(sysdate - 1, 'D'))能给我解释下这段SQL的意思吗 取出来的数据也少
and a.t1 <= to_date(to_char(add_months(sysdate, -1), 'yyyymm'),
'yyyymm')),改成 -> where a.t1 >= to_date(to_char(sysdate, 'yyyy') || '01', 'yyyymm')
and a.t1 < to_date(to_char(sysdate, 'yyyymm')||'01 00:00:00','yyyymmdd hh24:mi:ss')),
and a.t1 <= sysdate + (7 - to_char(sysdate - 1, 'D'))能给我解释下这段SQL的意思吗 取出来的数据也少==================改成=========================== where a.t1 >= to_date(to_char(sysdate - (to_char(sysdate - 1, 'D') - 1),'yyyymmdd')||' 00:00:00','yyyymmdd hh24:mi:ss')
and a.t1 < to_date(to_char(sysdate + (7 - to_char(sysdate - 1, 'D')+1), 'yyyymmdd')||' 00:00:00','yyyymmdd hh24:mi:ss')