给出具体的数据和想要的结果。sql语句很简单的,关键是看你要的结果。条件类似于: where to_date(字段A||字段B,'yyyymm') ....
select * from A where to_date(A.year||A.month,'yyyymm')<='201103' and to_date(A.year||A.month,'yyyymm')>='201001'这样可否?
select 年||月 from biao 这个就是你要的。
select * from tb where to_char(年) || right('0' || to_char(月),2) between ... and ...
--想用to_char()将year和month转换为字符,再将他们连接(||)起来, --最后用to_date()函数将其转换为指定格式的日期,即可与给定参数比较; --下面是我自己构建的测试 SQL> with t as( 2 select '2009' y,'09' m,'25' d,100 num from dual union all 3 select '2011','12','10',500 from dual union all 4 select '2001','08','12',5600 from dual union all 5 select '2010','01','21',589 from dual union all 6 select '2008','03','30',1253 from dual union all 7 select '2007','03','26',9870 from dual union all 8 select '2006','10','11',56 from dual union all 9 select '2010','12','28',10 from dual) 10 select to_date(to_char(y)||to_char(m)||to_char(d),'yyyy-mm-dd') dates,num 11 from t 12 where to_date(to_char(y)||to_char(m),'yyyy-mm') 13 between to_date('2006-10','yyyy-mm') and 14 to_date('2010-12','yyyy-mm') 15 order by dates; /* DATES NUM ----------- ---------- 2006-10-11 56 2007-3-26 9870 2008-3-30 1253 2009-9-25 100 2010-1-21 589 2010-12-28 106 rows selected /*
select * from A where to_date(A.year||A.month,'yyyymm')<='201103'
and to_date(A.year||A.month,'yyyymm')>='201001'这样可否?
--想用to_char()将year和month转换为字符,再将他们连接(||)起来,
--最后用to_date()函数将其转换为指定格式的日期,即可与给定参数比较;
--下面是我自己构建的测试
SQL> with t as(
2 select '2009' y,'09' m,'25' d,100 num from dual union all
3 select '2011','12','10',500 from dual union all
4 select '2001','08','12',5600 from dual union all
5 select '2010','01','21',589 from dual union all
6 select '2008','03','30',1253 from dual union all
7 select '2007','03','26',9870 from dual union all
8 select '2006','10','11',56 from dual union all
9 select '2010','12','28',10 from dual)
10 select to_date(to_char(y)||to_char(m)||to_char(d),'yyyy-mm-dd') dates,num
11 from t
12 where to_date(to_char(y)||to_char(m),'yyyy-mm')
13 between to_date('2006-10','yyyy-mm') and
14 to_date('2010-12','yyyy-mm')
15 order by dates;
/*
DATES NUM
----------- ----------
2006-10-11 56
2007-3-26 9870
2008-3-30 1253
2009-9-25 100
2010-1-21 589
2010-12-28 106 rows selected
/*