日期:2014-05-17  浏览次数:20937 次

如何用sql取到3个月的数据
比如数据库有个字段 yearMonth
都是2007-01,2007-02,2007-03,2007-05这样的数据
程序里传入参数2007-03
我要把yearMonth等于2007-01,2007-02,2007-03的三条数据搜出来,sql的where条件怎么写

------解决方案--------------------
这个只要简单的修改语句即可.
譬如表格叫Ytab,
select * from Ytab where YearMonth bwtween iYearMonth 
and to_char(add_months(to_date(iYearMonth,'yyyy-mm'),-2),'yyyy-mm');

------解决方案--------------------
如果yearMonth 是字符型的話就
WHERE yearMonth IN (TO_CHAR(ADD_MONTHS(to_date('200703','yyyy-mm'),-1),'YYYY-MM'),ADD_MONTHS(to_date('200703','yyyy-mm'),-2),'YYYY-MM'),'2007-03')
------解决方案--------------------
WHERE yearMonth BETWEEN '2007-03' AND TO_CHAR(ADD_MONTHS(to_date('200703','yyyy-mm'),-2)
------解决方案--------------------
SQL> CREATE TABLE MYTEST(YEARMONTH NVARCHAR2(30));

Table created

SQL> INSERT INTO MYTEST SELECT '2007-01' FROM DUAL
2 UNION SELECT '2007-02' FROM DUAL
3 UNION SELECT '2007-03' FROM DUAL
4 UNION SELECT '2007-05' FROM DUAL;

4 rows inserted

SQL> SELECT * FROM MYTEST
2 WHERE YEARMONTH=TO_CHAR(ADD_MONTHS(TO_DATE('2007-03','YYYY-MM'),-1),'yyyy-mm')
3 OR YEARMONTH=TO_CHAR(ADD_MONTHS(TO_DATE('2007-03','YYYY-MM'),-2),'yyyy-mm')
4 OR YEARMONTH='2007-03';

YEARMONTH
------------------------
2007-01
2007-02
2007-03