没说ä»ä¹æ°æ®åºï¼sqlserver为ä¾
å建æµè¯è¡¨
create table test
(å§å varchar(10),
äºç± varchar(10),
å¼å§æ¶é´ datetime,
ç»ææ¶é´ datetime)
insert into test values ('å¼ ä¸','äºå','2014-07-21','2014-07-23')
insert into test values ('æå','äºå','2014-07-22','2014-07-24')
insert into test values ('çæ¦','ç
å','2014-07-23','2014-07-24')
æ§è¡
select t.æ¶é´,
sum(case when t.äºç±='ç
å' then 1 else 0 end) ç
å,
sum(case when t.äºç±='äºå' then 1 else 0 end) äºå
from
(select a.å§å,a.äºç±,a.å¼å§æ¶é´+b.number æ¶é´
from test a,
(select number from master..spt_values
where type='P' and number<=(select max(DATEDIFF(DAY,å¼å§æ¶é´,ç»ææ¶é´)) from test)) b
where DATEDIFF(DAY,a.å¼å§æ¶é´,a.ç»ææ¶é´)>=b.number) t
group by t.æ¶é´
ç»æ
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/d8f9d72a6059252d5fb6eb37379b033b5bb5b964?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)