oracle两个日期间数据,Oracle SQL - 按两个日期D1和D2之间的星期数据组数据(Oracle SQL - group data by week between two date D1 ...

Oracle SQL - 按两个日期D1和D2之间的星期数据组数据(Oracle SQL - group data by week between two date D1 and D2)

#Oracle我有一个与日期相关的记录，并且我会按两个日期D1和D2之间的星期分组。

我的会议桌是，

id_Meeting | meeting_date

-----------+--------------

1 | 07/04/2017

2 | 10/06/2017

3 | 17/04/2017

4 | 08/05/2017

5 | 19/04/2017

6 | 12/05/2017

7 | 06/04/2017

8 | 04/04/2017

现在我打印Date1 = 01/04/2017和Date1 = 01/05/2017之间的所有行，

Week | nb_meeting_by_week

-----+--------------------

w1 | 3

w2 | 0

w3 | 2

w4 | 3

有任何想法吗？

我试过的是，

Select

TRUNC((TO_DATE(m.meeting_date, 'YYYYMMDD') - TO_DATE('01/04/17', 'dd-MM-yy'))/7 +1)

as week,

count(*) as nb_meeting_by_week

from meeting m

Where TO_DATE(m.meeting_date, 'YYYYMMDD') between '01/04/17' and '01/05/17'

Group by

TRUNC((TO_DATE(m.meeting_date, 'YYYYMMDD') - TO_DATE('01/04/17', 'dd-MM-yy'))/7 +1);

但是我没有得到第二周[w2 | 0]，因为本周我没有见面。 但是，我在所有几周没有开会的情况下都会有0。

#Oracle i have a records related to dates, and i would to group by week between two date D1 and D2.

My meeting table is,

id_Meeting | meeting_date

-----------+--------------

1 | 07/04/2017

2 | 10/06/2017

3 | 17/04/2017

4 | 08/05/2017

5 | 19/04/2017

6 | 12/05/2017

7 | 06/04/2017

8 | 04/04/2017

Now I print all rows like that between Date1=01/04/2017 and Date1=01/05/2017,

Week | nb_meeting_by_week

-----+--------------------

w1 | 3

w2 | 0

w3 | 2

w4 | 3

Any ideas?

What i try me is,

Select

TRUNC((TO_DATE(m.meeting_date, 'YYYYMMDD') - TO_DATE('01/04/17', 'dd-MM-yy'))/7 +1)

as week,

count(*) as nb_meeting_by_week

from meeting m

Where TO_DATE(m.meeting_date, 'YYYYMMDD') between '01/04/17' and '01/05/17'

Group by

TRUNC((TO_DATE(m.meeting_date, 'YYYYMMDD') - TO_DATE('01/04/17', 'dd-MM-yy'))/7 +1);

But i don't get the second week [w2 | 0] because i don't have meeting in this week. But me i would have 0 for all weeks that have zero meetings.

原文：https://stackoverflow.com/questions/43375246

更新时间：2020-01-16 13:21

最满意答案

您必须首先生成所有星期，因为它们不在数据库中(即表中每周没有记录)。 这是通过递归查询完成的。 然后外面加入你的餐桌。

with weeks(weekno, from_date, max_date) as

(

select 1 as weekno, date '2017-04-01' as from_date, date '2017-05-01' as max_date

from dual

union all

select weekno + 1 as weekno, from_date + 7 as from_date, max_date

from weeks

where from_date + 7 <= max_date

)

select weeks.weekno, count(mbw.meeting_date)

from weeks

left outer join nb_meeting_by_week mbw

on mbw.meeting_date >= weeks.from_date

and mbw.meeting_date < weeks.from_date + 7

and mbw.meeting_date <= weeks.max_date

group by weeks.weekno

order by weeks.weekno;

You must first generate all weeks on-the-fly, as they are not in your database (i.e. there isn't a record for every week in the table). This is done with a recursive query. Then outer join your table.

with weeks(weekno, from_date, max_date) as

(

select 1 as weekno, date '2017-04-01' as from_date, date '2017-05-01' as max_date

from dual

union all

select weekno + 1 as weekno, from_date + 7 as from_date, max_date

from weeks

where from_date + 7 <= max_date

)

select weeks.weekno, count(mbw.meeting_date)

from weeks

left outer join nb_meeting_by_week mbw

on mbw.meeting_date >= weeks.from_date

and mbw.meeting_date < weeks.from_date + 7

and mbw.meeting_date <= weeks.max_date

group by weeks.weekno

order by weeks.weekno;

2017-04-13

相关问答

三目运算符，问号前条件成立则取冒号左边，不不成立则取冒号右边。 if(d1

参数列表中的**具有特殊含义，正如本教程的第4.7节所述 。 用**kwargs传递的字典(或字典式)对象被扩展为可调用的关键字参数，就像*args被扩展为单独的位置参数。 ** in argument lists has a special meaning, as covered in section 4.7 of the tutorial. The dictionary (or dictionary-like) object passed with **kwargs is expanded i

...

我会找到当年的第一个星期一，然后使用DATEADD添加当天的星期数 select STAFFID

, count(*) as appointment_ct

, datepart(iso_week, appointment_date) as iso_wk --this returns the week # of the year as an int

, dateadd(week, datepart(week, DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEK

...

您必须首先生成所有星期，因为它们不在数据库中(即表中每周没有记录)。 这是通过递归查询完成的。 然后外面加入你的餐桌。 with weeks(weekno, from_date, max_date) as

(

select 1 as weekno, date '2017-04-01' as from_date, date '2017-05-01' as max_date

from dual

union all

select weekno + 1 as weekno, from_

...

表达式(d1 + d2)从operator +( return temp )返回时产生一个临时对象。 从'temp'创建临时文件是通过复制构造函数完成的。 然后分配给d3。 The expression (d1 + d2) yields a temporary object while returning from operator+ (return temp). The creation of the temporary from 'temp' is done through the copy

...

也许在php中尝试这样的东西 strftime(%U, strtotime($mydate))

maybe try something like this in php strftime(%U, strtotime($mydate))

如果在任何给定的时间点，“前一周”表示在星期日开始的最近午夜结束的七天时段，那么这样的事情应该有效： with inputs (dt) as (

select sysdate from dual union all

select sysdate + 1 from dual union all

select sysdate + 2 from dual union all

select sysdate + 3 from dual

...

您好，你可以通过添加additiobnal条件扩展给定的查询，这应该工作。 如果您遇到任何问题，请告诉我 select d1,SUM(case

when d1=d2 then cnt

end

) as count_d1_d2,

SUM(case

when d1=d2 then sm

end

) as SUM_d

...