Oracle先按周分，当周含两个月份，再按月分，怎么写SQL语句？大神求助!!!

Question

我现在选择日期条件2019-05-28至2019-06-07(日历:5.27周一，，，6.02周日。6.03周一，，，6.09周日)，我想得出的结果是:2019-05-27至2019-05-31、2019-06-01至2019-06-02、2019-06-03至2019-06-09

Answer 1

with temp as(
select trunc(sysdate)-level createday
 from dual
connect by level<100
)select 
substr(listagg (to_char(createday,'yyyy/mm/dd'),'-') WITHIN GROUP (ORDER BY createday),1,10)||'-'
||substr(listagg (to_char(createday,'yyyy/mm/dd'),'-') WITHIN GROUP (ORDER BY createday),-10,10) week
,'' 周一,'' 周二,'' 周三,'' 周四,'' 周五,'' 周六,'' 周日
from temp 
group by to_char(createday,'yyyymm'),to_char(createday,'w')

Answer 2

with temp as(
select trunc(sysdate)-level createday
 from dual
connect by level<100
)select 
min(to_char(createday,'yyyy/mm/dd'))||'-'||max(to_char(createday,'yyyy/mm/dd'))
,'' 周一,'' 周二,'' 周三,'' 周四,'' 周五,'' 周六,'' 周日
from temp 
group by to_char(createday,'yyyymm'),to_char(createday,'w')

Answer 3

with temp as(
select
ROW_NUMBER() OVER(ORDER BY trunc(sysdate)-level) RN,
to_char(trunc(sysdate)-level,'yyyymm') createmonth,
trunc(sysdate)-level createday
 from dual
connect by level<100
)
select to_char(min(createday),'yyyy/mm/dd')||'-'||to_char(max(createday),'yyyy/mm/dd')
from(
select *
from temp
model 
partition by (createmonth)
dimension by (rn)　
measures(createday,0 week)　　　
rules(　
    week[rn]=    CASE WHEN  nvl(to_char(createday[CV()-1],'day'),'星期日')='星期日' then  nvl(week[cv()-1],0)+1 ELSE week[cv()-1] END
))
group by createmonth,week order by 1

Answer 4

七楼的SQL语句结果,,,,,效果跟按月来一样的

Answer 5

七楼的SQL语句结果,,,,,效果跟按月来一样的

Answer 6

要不，再copy一下？

Answer 7

我就是上面的语句，你先把最外面的select 去掉，看看里面select 你的结果是什么

Answer 8

with temp as(
select
ROW_NUMBER() OVER(ORDER BY trunc(sysdate)-level) RN,
to_char(trunc(sysdate)-level,'yyyymm') createmonth,
trunc(sysdate)-level createday
 from dual
connect by level<100
)select *
from temp
model 
partition by (createmonth)
dimension by (rn)　
measures(createday,0 week)　　　
rules(　
    week[rn]=    CASE WHEN  nvl(to_char(createday[CV()-1],'day'),'星期日')='星期日' then  nvl(week[cv()-1],0)+1 ELSE week[cv()-1] END
)
order by 3这个，看下week 是不是一周一个值

Answer 9

我给我朋友运行(Oracle最新版本)，运行结果是对的，跟你显示的一样，

Answer 10

我给我朋友运行(Oracle最新版本)，运行结果是对的，跟你显示的一样，

他的PL/SQL development查看属性，12g。通过select * from v$version查看，都是11.2.0.1.0

Answer 11

with temp as(
select
ROW_NUMBER() OVER(ORDER BY trunc(sysdate)-level) RN,
to_char(trunc(sysdate)-level,'yyyymm') createmonth,
trunc(sysdate)-level createday
 from dual
connect by level<100
)select *
from temp
model 
partition by (createmonth)
dimension by (rn)　
measures(createday,0 week,to_char(createday,'day') weekday)　　　
rules(　
    week[rn]=    CASE WHEN  nvl(to_char(createday[CV()-1],'day'),'星期日')='星期日' then  nvl(week[cv()-1],0)+1 ELSE week[cv()-1] END
)
order by 3看下这个语句的结果

Answer 12

16楼SQL语句运行

Answer 13

19楼Sql语句运行结果

Answer 14

你的weekday 是英文，把星期日改成 sunday，
或者改成
 nvl(to_char(createday[CV()-1],'d'),1)=1

Answer 15

改成nvl(to_char(createday[CV()-1],'d'),1)=1，我的结果是对的！不管版本，谢谢:)

Answer 16

我还有个问题，，，恕我愚钝，我若将查询条件传至这个SQL语句，在temp里如何改，如2019/05/28-2019/06/07起止日期

Answer 17

我觉得level就用不了了

Answer 18

引用 2 楼 ZJHZ_叶的回复:with temp as(
select trunc(sysdate)-level createday
 from dual
connect by level<100
)select 
min(to_char(createday,'yyyy/mm/dd'))||'-'||max(to_char(createday,'yyyy/mm/dd'))
,'' 周一,'' 周二,'' 周三,'' 周四,'' 周五,'' 周六,'' 周日
from temp 
group by to_char(createday,'yyyymm'),to_char(createday,'w')
谢谢！不过结果不是想要的

Answer 19

我觉得level就用不了了
select
ROW_NUMBER() OVER(ORDER BY to_date('20190528','yyyymmdd')+level-1) RN,
to_char(to_date('20190528','yyyymmdd')+level-1,'yyyymm') createmonth,
to_date('20190528','yyyymmdd')+level-1 createday
 from dual
connect by level<=to_date('20190607','yyyymmdd')-to_date('20190528','yyyymmdd')+1

Answer 20

with temp as(
select trunc(sysdate)-level createday
 from dual
connect by level<100
)select 
substr(listagg (to_char(createday,'yyyy/mm/dd'),'-') WITHIN GROUP (ORDER BY createday),1,10)||'-'
||substr(listagg (to_char(createday,'yyyy/mm/dd'),'-') WITHIN GROUP (ORDER BY createday),-10,10) week
,'' 周一,'' 周二,'' 周三,'' 周四,'' 周五,'' 周六,'' 周日
from temp 
group by to_char(createday,'yyyymm'),to_char(createday,'w')

Answer 21

SELECT
A || '至' || b AS 区间
FROM
(
SELECT
A .MW,
MIN (A .DT) AS A,
MAX (A .DT) AS b
FROM
(
SELECT
TO_CHAR (dt, 'yyyy-mm-dd') AS dt,
TO_CHAR (dt, 'MM') || TO_CHAR (dt, 'W') AS MW
FROM
(
SELECT
TRUNC (SYSDATE - 1 + ROWNUM - 30, 'dd') dt
FROM
dual CONNECT BY ROWNUM <= 30
)
) A
GROUP BY
A .mw
ORDER BY
mw
)；

Answer 22

这么基础的groupby怎么连model都整出来了
with tab1 as (
select to_date('20190520', 'yyyymmdd') + level dt
  from dual
connect by level <= 20
)
select min(t1.dt),
       max(t1.dt)
  from tab1 t1
 group by to_char(t1.dt, 'mm'), to_char(t1.dt + 1, 'ww')
 order by min(t1.dt)
;

Answer 23

你这个我前面写过了，to_char(,'w')/to_char(,'ww')并不是从周一开始的，而是当月(年)1-7号算一周,然后每7天算一周

Answer 24

你这个我前面写过了，to_char(,'w')/to_char(,'ww')并不是从周一开始的，而是当月(年)1-7号算一周,然后每7天算一周

那就改成iw呗

Answer 25

你这个我前面写过了，to_char(,'w')/to_char(,'ww')并不是从周一开始的，而是当月(年)1-7号算一周,然后每7天算一周

那就改成iw呗谢谢

Answer 26

with data1 as (
select (trunc(sysdate) -level) as days,
       to_char(sysdate-level,'day') weekday,
       to_char(sysdate-level+1,'ww') as week,
       last_day(sysdate-level) as lastday
  from dual 
connect by level <=200)
  select min(days),max(days),week,lastday
    from data1
    group by week,lastday
    order by week desc

Answer 27

在发帖子那周的周末我已解决,当时因为后续有任务,没有及时回复
首先：
（我要统计的报表结果，有周期字段、用户字段、总数字段；情况：多个用户在某日的某时有多个记录数据（若统计一天，算一天的数据条数，若统计一周，算七天的数据条数，以此类推，若我的周要分细点，则要实现“2019.7.22至2019.7.28、2019.7.29至2019.07.31、2019.8.1至2019.8.4”，统计周，算对应的‘2019.7.29至2019.07.31’时间范围内的数据条数、算对应的‘2019.8.1至2019.8.4’的数据条数【都是闭区间的--》2019.8.1 00:00:00至2019.8.4 23:59:59】）
然后：
我以为我想要的效果"2019.7.22至2019.7.28、2019.7.29至2019.07.31、2019.8.1至2019.8.4"只能却一定通过SQL语句实现，其实不是的。我可以获取SQL语句得出的结果用Java代码再处理一下，得到图片里“查询结果列表”里的结果！！！
若只考虑：同一用户在同一周的不同日不同时有不同数据，但那一周存在俩个月份（我觉得：先实现这种情况，后面就不难），要实现：统计某用户在一周的记录总数，且周存在俩个月份，要细分）
思路：
若008用户在7.29与7.30的不同时分秒有多个记录操作，00香在8.01的不同时分秒有多个记录操作，
想：7.29-7.31   008    56
       8.1-8.04  00香   13【日期  用户  记录总数】
后面改着改着，成这样了：
【注意：第三条与第四条正是简单情况要实现的！！！必须加上to_char(t.sj ,'iw')否则报异常】select row_number() over(order by to_char(min(t.sj),'YYYY-MM-DD') asc,to_char(max(t.sj),'YYYY-MM-DD') asc,yh.yh asc) xh,
    to_char(min(t.sj),'YYYY-MM-DD') mindt,to_char(max(t.sj),'YYYY-MM-DD') maxdt
     ,TO_CHAR(t.sj, 'iw') zrz--自然周(数字,表示一年的第几周)
       ,yh.yh xm
       ,count(1) zsl,sum(case when t.zt<>0  then 1 else 0 end) as wdq,sum(case when t.zt=0 then 1 else 0 end) as ydq
       from cxx t
       left join YH yh on t.ry=yh.h    
     WHERE  t.sj between to_date('2019-04-25 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2019-09-25 23:59:59','yyyy-mm-dd hh24:mi:ss')      
    group by to_char(t.sj ,'iw'),to_char(t.sj,'mm'),yh.yh; 
第三条的介绍，001用户在自然周zrz=31的mindt至maxdt时间段(闭区间)有数据,总数为56
所以第四条的介绍也是一样的：00香用户在自然周zrz=31的mindt至maxdt时间段(闭区间)有数据,总数为13
mindt至maxdt时间段(闭区间)表示：mindt到maxdt这几天都是有记录数据，
mindt与maxdt不一定指的是一周的周一与周日，但一定在该周内，
则用java代码处理一下：
代码蛮多的，反正做判断的思路：
mindt大于周的周一,但小于下个月的第一天对应的星期,则mindt值改为周一,maxdt小于周的周日,也小于下个月的第一天对应的星期,则maxdt值改为这个月的最后一天对应的星期,
若mindt大于周一也大于下月第一天对应的星期，maxdt小于周日但大于下月第一天对应的星期，则分别改下月第一天对应的星期、周日注: 不可能mindt小于下月第一天对应的星期而maxdt大于下月第一天对应的星期,
还要注意若maxdt等于下月第一天对应的星期或周日等等情况。对，方法很麻烦，但是我真的想不出如何用SQL语句实现，怎么写都会有点问题（不是想要的答案），，，方法很多种就是要花时间研究，但是模块花费时间太长，同事在催进度，我只能这样写