oracle sql查询列出上个月的所有日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4644562/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle sql query to list all the dates of previous month
提问by Dead Programmer
Guys i have a requirement to list all the dates of the previous month like below
伙计们，我需要列出上个月的所有日期，如下所示
20101201
20101202
20101203
20101204
20101205
..
..
..
..
..
..
..
..
20101231
kindly let me know if any better way to do than this query.
请让我知道是否有比此查询更好的方法。
select TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYYMMDD')-(level-1) as
EACH_DATE from dual A connect by level
< (TO_NUMBER(TO_CHAR(TRUNC(SYSDATE,'MM')-1,'DD'))+1)
Also please let me know the problem with this query it says "missing right parenthesis"
另外请让我知道这个查询的问题，它说“缺少右括号”
SELECT /*+ PARALLEL (A,8) */ /*+ DRIVING_STATE */
TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM')-1,'MM'),'MONYYYY') "MONTH", TYPE AS "TRAFF", COLUMN, A_COUN AS "A_COUNT",COST FROM DATA_P B WHERE EXISTS
(
select TO_NUMBER(TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYYMMDD')-(level-1)) EACH_DATE
from dual A connect by level < TO_NUMBER(TO_CHAR(TRUNC(SYSDATE,'MM')-1,'DD')+1)
WHERE A.EACH_DATE = B.DATE order by EACH_DATE ASC
)
emphasized text
强调文本
回答by Justin Cave
It sounds like you want something like this
听起来你想要这样的东西
SQL> ed
Wrote file afiedt.buf
1 select to_char( add_months(trunc(sysdate,'MM'),-1) + level - 1,
2 'YYYYMMDD' )
3 from dual
4 connect by level <=
5 last_day(add_months(trunc(sysdate,'MM'),-1)) -
6 add_months(trunc(sysdate,'MM'),-1) +
7* 1
SQL> /
TO_CHAR(
--------
20101201
20101202
20101203
20101204
20101205
20101206
20101207
20101208
20101209
20101210
20101211
20101212
20101213
20101214
20101215
20101216
20101217
20101218
20101219
20101220
20101221
20101222
20101223
20101224
20101225
20101226
20101227
20101228
20101229
20101230
20101231
31 rows selected.
回答by M Naveed Raza
for current month :
本月：
SELECT TO_CHAR (TRUNC (SYSDATE, 'MM'), 'YYYYMMDD')+(LEVEL - 1) each_date
FROM DUAL a
CONNECT BY LEVEL < (TO_NUMBER (TO_CHAR (TRUNC (SYSDATE, 'MM') - 1, 'DD'))+1)
回答by sat.oh
This may be a little easier to understand:
这可能更容易理解：
select TO_CHAR(d, 'YYYYMMDD')
from (
select ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1) + (ROWNUM - 1) d
from DUAL connect by level <= 31
)
where d < TRUNC(SYSDATE, 'MM')
However, the "connect by level" method is the most clear, and as described here, faster way to generate sequence of numbers. I don't think there is no way to dramatically improve your query.
然而，“连接的水平”的方法是最清楚的，并且描述这里，更快的方式生成的数字序列。我认为没有办法显着改善您的查询。
回答by Erich Kitzmueller
A bit of add_months would definitely make it better, as in e.g.
一点 add_months 肯定会让它变得更好，例如
select to_char(x,'yyyymmdd') from (
select add_months(trunc(sysdate,'MONTH'),-1)+rownum-1 x from all_objects
) where x<trunc(sysdate,'MONTH');
回答by sjngm
As far as the right parenthesis is concerned, you are trying to concatenate strings the wrong way:
就右括号而言，您试图以错误的方式连接字符串：
select TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYYMMDD')-(level-1) as
should work:
应该管用：
select TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYYMMDD') || '-' || To_Char(level-1) as
Obviously you don't want the concatenation to happen. Therefore, I think you actually want to add the level to the TRUNC()-part
显然，您不希望串联发生。因此，我认为您实际上想将级别添加到TRUNC()-part
Fix:
使固定：
select TO_CHAR(TRUNC(SYSDATE,'MM') - 1 + level - 1,'YYYYMMDD') as
EACH_DATE from dual A connect by level
< (TO_NUMBER(TO_CHAR(TRUNC(SYSDATE,'MM')-1,'DD'))+1)
