--建议æ建ä¸ä¸ªæ¥å表,è½å¤ä½ç°åºå·¥ä½æ¥åéå·¥ä½æ¥,è¿æ ·ç®çè¯æ´å;
--ä¸é¢æ¯ç®ååé¤åä¼çSQLæ¹æ³
--mssql:
select a,
b,
COUNT(case
when d in (1, 7) then
null
else
1
end)
from (select a, b, c, DATEPART(dw, DATEADD(DAY, t1.number, a)) d
from (select a, b, DATEDIFF(day, a, b) as c
from (select '2012-01-01' as a, '2012-12-31' as b) t) t,
master .. spt_values t1
where t1.type = 'P'
and t1.number < c) t2
group by a, b;
--oracle:
select a,
b,
count(case
when d in (1, 7) then
null
else
1
end)
from (select a, b, a + level - 1 as c, to_char(a + level - 1, 'd') d
from (select to_date('2012-01-01', 'yyyy-mm-dd') a,
to_date('2012-12-31', 'yyyy-mm-dd') b
from dual)
connect by level <= b - a)
group by a, b
追é®![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/8ad4b31c8701a18b188c57d09b2f07082938fece?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
追çè¿ä¸ªæ¯å¥? ç®å½æåºåºå¤å¤©æ°?è¿ä¸ªè¡¨éåºè¯¥æå段è½å¤å¤æå
¥èæ¶é´å离èæ¶é´çå§