SQL中取一个月的数据的处理方式

来源：互联网发布：协同软件编辑：程序博客网时间：2024/05/13 02:00

前提：

场景设置：表T，其中有属性date表示表中数据的更新时间。

问题：取一个月的数据：2015 年1月的数据。

取前一个月的数，当前月份未知。

一，取一个月的数据

对于知道确定月份的需求，SQL处理很简单：

SELECT * FROM T WHERE TO_CHAR(date,'yyyy-mm')='2015-01';

二，取前一个月的数

这个就需要从sysdate着手，需要从此获取到前一个月的具体月份值：

参考：http://www.cnblogs.com/mybi/archive/2012/09/05/2671761.html

SELECT CONCAT(TO_CHAR(SYSDATE, 'yyyy') ||
TO_CHAR(ADD_MONTHS(SYSDATE, -1), '-MM-'), '01 00:00:00') START_TIME
,

CONCAT(TO_CHAR(SYSDATE, 'yyyy') ||
TO_CHAR(ADD_MONTHS(SYSDATE, -1), '-MM-') ||
TO_CHAR(LAST_DAY(SYSDATE), 'dd '), '23:59:59') END_TIME
FROM DUAL;

结果：

解读：

1,CONCAT(字串1, 字串2, 字串3, ...):

将字串1、字串2、字串3，等字串连在一起。请注意，Oracle的CONCAT()只允许两个参数；换言之，一次只能将两个字串串连起来。不过，在Oracle中，我们可以用'||'来一次串连多个字串。

MySQL: CONCAT()
Oracle: CONCAT(), ||
SQL Server: +

2,T0_CHAR(,):

参考：http://www.cnblogs.com/reborter/archive/2008/11/28/1343195.html

表 5-7. 格式化函数

函数返回描述例子to_char(timestamp, text)text把 timestamp 转换成 stringto_char(timestamp 'now','HH12:MI:SS')to_char(int, text)text把 int4/int8 转换成 stringto_char(125, '999')to_char(float, text)text把 float4/float8 转换成 stringto_char(125.8, '999D9')to_char(numeric, text)text把 numeric 转换成 stringto_char(numeric '-125.8', '999D99S')to_date(text, text)date把 string 转换成 dateto_date('05 Dec 2000', 'DD Mon YYYY')to_timestamp(text, text)date把 string 转换成 timestampto_timestamp('05 Dec 2000', 'DD Mon YYYY')to_number(text, text)numeric把 string 转换成 numericto_number('12,454.8-', '99G999D9S')

表 5-8. 用于 date/time 转换的模板

模板
描述
HH
一天的小时数 (01-12)
HH12
一天的小时数 (01-12)
HH24
一天的小时数 (00-23)
MI
分钟 (00-59)
SS
秒 (00-59)
SSSS
午夜后的秒 (0-86399)
AM or A.M. or PM or P.M.
正午标识（大写）
am or a.m. or pm or p.m.
正午标识（小写）
Y,YYY
带逗号的年（4 和更多位）
YYYY
年（4和更多位）
YYY
年的后三位
YY
年的后两位
Y
年的最后一位
BC or B.C. or AD or A.D.
年标识（大写）
bc or b.c. or ad or a.d.
年标识（小写）
MONTH
全长大写月份名（9字符）
Month
全长混合大小写月份名（9字符）
month
全长小写月份名（9字符）
MON
大写缩写月份名（3字符）
Mon
缩写混合大小写月份名（3字符）
mon
小写缩写月份名（3字符）
MM
月份 (01-12)
DAY
全长大写日期名（9字符）
Day
全长混合大小写日期名（9字符）
day
全长小写日期名（9字符）
DY
缩写大写日期名（3字符）
Dy
缩写混合大小写日期名（3字符）
dy
缩写小写日期名（3字符）
DDD
一年里的日子(001-366)
DD
一个月里的日子(01-31)
D
一周里的日子(1-7；SUN=1)
W
一个月里的周数
WW
一年里的周数
CC
世纪（2 位）
J
Julian 日期（自公元前4712年1月1日来的日期）
Q
季度
RM
罗马数字的月份（I-XII；I=JAN）－大写
rm
罗马数字的月份（I-XII；I=JAN）－小写
所有模板都都允许使用前缀和后缀修改器。模板里总是允许使用修改器。前缀　'FX' 只是一个全局修改器。
表 5-9. 用于日期/时间模板 to_char() 的后缀

后缀描述例子FM填充模式前缀FMMonthTH大写顺序数后缀DDTHth小写顺序数后缀DDTHFX固定模式全局选项（见下面）FX Month DD DaySP拼写模式（还未实现）DDSP
用法须知：

如果没有使用 FX 选项，to_timestamp 和 to_date 忽略空白。FX 必须做为模板里的第一个条目声明。

反斜杠（"\"）必须用做双反斜杠（"\\"），例如'\\HH\\MI\\SS'。

双引号（'"'）之间的字串被忽略并且不被分析。如果你想向输出写双引号，你必须在双引号前面放置一个双反斜杠（'\\'），例如'\\"YYYY Month\\"'。

to_char 支持不带前导双引号（'"'）的文本，但是在双引号之间的任何字串会被迅速处理并且还保证不会被当作模板关键字解释（例如：'"Hello Year: "YYYY'）。

参考：《Oracle 中的 TO_DATE 和 TO_CHAR 函数日期处理》http://www.cnblogs.com/jiangchongwei/archive/2011/05/02/2034273.html

此参考文章，对时间的处理，列举的非常详尽，但是缺少说明和解析。
参考：《TO_CHAR_转换函数的用法》http://blog.csdn.net/huangbiao86/article/details/6607939
此参考文章中，列举了FORMAT中所有符号的解析，在FOMCAT中可以自行组合：

TO_CHAR ( date [, fmt [, 'nlsparam'] ])
它的作用是将一个日期date转换成一个指定格式fmt的字符串。有关nlsparam基本可以不管，它是用来确定返回的日期的名字以及缩写词等的形式，如你的数据库语言如果是英语，那么返回的月份表示可能是类似这样的“MAY”；而如果你的数据库语言是汉语，那么返回的月份表示可能是类似这样的“五月”。nlsparam就是用来改变这些显示，如假设你的数据库语言是汉语，而你像把它变成英语的表示，则可以这样设置：nls_date_language=’english’。99％可以不用管这个参数。如下面这个例子：
10:13:29 SQL> select to_char(sysdate,'yyyy-mm-dd') today from dual;
可能大家问题最多的就是fmt这个格式设置有这些：
元素
表示含义
-
/
,
.
;
:
“test”
这些标点符号和具体的字符串，最终会显示出来，其实他们启到的作用就是分隔，如下面这个日期：
2007-9-16
这里符合 “-”就启到了分隔年月日的作用。
AD
A.D.
公元后
BC
B.C.
公元前
AM
A.M.
早上，上午
PM
P.M.
下午
D
一周的第几天，星期天是1
DAY
星期几，，一共用9个字符大小来显示，如果没有这么长，名称的右边用空格来填充
DD
一个月的第几天，【1～31】
DDD
一年的第几天，【1～366】
DY
星期几，但是是简称，如周一可能就是MON
FF[0..9]
这个是用于timestamp类型的，用于表示将秒分成多少位数来表示，在FF后面跟上一个0到9的数字。
HH
小时，【1～12】
HH12
小时，【1～12】
HH24
小时，【0～23】
MI
分钟，【0～59】
MM
月份，【01~12】
MON
月份，但是是月份的简称，如1月可能是JAN来表示
MONTH
月份，月份的名称，如9月可能是september来表示，一共用9个字符大小来显示，如果没有这么长，名称的右边用空格来填充
Q
季节，【1，2，3，4】分别代表春夏秋冬
SS
秒，【0～59】
SSSS
从午夜0秒开始计算到现在的秒数
WW
一年的第几个周，【1～53】
W
一个月的第几个周，【1～5】
Y,YYY
年份，中间有个逗号
YEAR
SYEAR
年份，用字符串显示，S表示指示公元前（BC）使用“-”表示
YYYY
SYYY
4个数字的年份，S表示指示公元前（BC）使用“-”表示
YYY
YY
Y
分别表示年份的后面三个、两个、一个数字

3，add_months():

格式：ADD_MONTHS(DATE,NUMBER)即ADD_MONTHS(日期，数字）
ADD_MONTHS函数在输入日期上加上指定的几个月返回一个新的日期。如果给出一负数，返回值日期之前几个月日期。
ADD_MONTHS(DATE,NUMBER)中的NUMBER应当是整数，给出小数时，正数被截为小于该数的最大整数，负数被截为大于该数的最小整数。
例：add_months(to_date('29-Feb-96','d-mon-yyyy'),-12.99)  返回 28－Feb-95
注：上例中29调整为28，是因为96年二月份最后一天是29号，而95年二月份最后一天是28号。
add_months(to_date('15-Nov-1961','d-mon-yyyy'),1) 返回   15-Dec-1961
add_months(to_date('30-Nov-1961','d-mon-yyyy'),1) 返回   31-Dec-1961
注：从30调整为31，为了保持都是对应最后一天。
add_months(to_date('31-Jan-1999','d-mon-yyyy'),1) 返回   28-Feb-1999
注：函数将31日调为28日，以使结果对应新一月的最后一天，因1999年2月只有28天。

4，last_day(d):

--时间点d当月份最后一天

ex.
   select sysdate, LAST_DAY(sysdate) LAST_DAY from dual;

   SYSDATE    LAST_DAY
   ---------- ---------
   21-SEP-07 30-SEP-07
参考：《oracle日期函数last_day得到当每月第一天与最后一天》http://www.360sdn.com/oracle/2013/0615/401.html
此文章中对last_day函数有多种运用方法。

0 0