select convert(ChangeDate,varchar(10),120) as day,
sum(case when status='yes' then 1 else 0 end) as constatusyes,
sum(case when Val='100' then 1 else 0 end) as ConVal100
from 表
group by convert(ChangeDate,varchar(10),120)
sum(case when status='yes' then 1 else 0 end) as constatusyes,
sum(case when Val='100' then 1 else 0 end) as ConVal100
from 表
group by convert(ChangeDate,varchar(10),120)
这个信息是我想抓的结果。最后统计的时候您上面的方法才可以。
DAY COL Status Val
2008-02-01 'A' 'NO' '100'
2008-02-02 'A' 'YES' '100'
2008-02-03 'A' 'YES' '120'
2008-02-01 'B' 'NO' '20'
2008-02-02 'B' 'NO' '20'
2008-02-03 'B' 'YES' '100'
2008-02-02 'C' 'YES' '100'
2008-02-03 'C' 'YES' '100'
2008-02-01 'D' 'NO' '100'
2008-02-02 'D' 'NO' '100'
2008-02-03 'D' 'NO' '100'
(一楼的convert都写反了,惭愧)
INSERT @TEST SELECT 'A','2007-12-01','Yes','120'
UNION ALL SELECT 'B','2007-12-01','Yes','70'
UNION ALL SELECT 'C','2008-02-02','YES','100'
UNION ALL SELECT 'D','2007-12-01','NO','100'
UNION ALL SELECT 'E','2008-09-01','YES','100'
DECLARE @TESTLOG TABLE (COL CHAR(1),ChangeDate DATETIME,Style CHAR(10),Old CHAR(10),New CHAR(10))
INSERT @TESTLOG SELECT 'A','2008-01-05 03:00:001','Status','Yes','No'
UNION ALL SELECT 'A','2008-02-02 05:04:001','Status','No','Yes'
UNION ALL SELECT 'A','2008-01-07 22:00:000','Val','20','130'
UNION ALL SELECT 'A','2008-01-25 22:00:000','Val','130','100'
UNION ALL SELECT 'A','2008-02-03 18:07:000','Val','100','120'
UNION ALL SELECT 'B','2008-02-01 03:08:001','Status','No','Yes'
UNION ALL SELECT 'B','2008-02-01 04:08:001','Status','Yes','No'
UNION ALL SELECT 'B','2008-02-03 08:08:001','Status','NO','Yes'
UNION ALL SELECT 'B','2008-02-03 09:10:001','Val','20','60'
UNION ALL SELECT 'B','2008-02-03 10:10:001','Val','60','100'
UNION ALL SELECT 'B','2008-05-03 02:10:001','Val','100','70'
--select * from @test
--select * from @testlogselect [day]=isnull(convert(varchar(10),a.ChangeDate,120),convert(varchar(10),b.CreateDate,120)),b.col
,Status=coalesce((select top 1 new from @testlog where col=b.col and style='Status' and ChangeDate<dateadd(d,1,isnull(convert(varchar(10),a.ChangeDate,120),convert(varchar(10),b.CreateDate,120))) order by ChangeDate desc)
,(select top 1 old from @testlog where col=b.col and style='Status' and ChangeDate>isnull(convert(varchar(10),a.ChangeDate,120),convert(varchar(10),b.CreateDate,120)) order by ChangeDate)
,(select CurrentStatus from @test where col=b.col and CreateDate<=isnull(convert(varchar(10),a.ChangeDate,120),convert(varchar(10),b.CreateDate,120))))
,Val=coalesce((select top 1 new from @testlog where col=b.col and style='Val' and ChangeDate<dateadd(d,1,isnull(convert(varchar(10),a.ChangeDate,120),convert(varchar(10),b.CreateDate,120))) order by ChangeDate desc)
,(select top 1 old from @testlog where col=b.col and style='Val' and ChangeDate>isnull(convert(varchar(10),a.ChangeDate,120),convert(varchar(10),b.CreateDate,120)) order by ChangeDate)
,(select CurrentVal from @test where col=b.col and CreateDate<=isnull(convert(varchar(10),a.ChangeDate,120),convert(varchar(10),b.CreateDate,120))))
into #t
from @testlog a right join @test b on a.col=b.col
where isnull(convert(varchar(10),a.ChangeDate,120),convert(varchar(10),b.CreateDate,120)) < '2008-02-04'
group by isnull(convert(varchar(10),a.ChangeDate,120),convert(varchar(10),b.CreateDate,120)),b.col--select * from #tselect b.day,a.col,a.Status,a.Val from #t a right join (select [day]='2008-02-01' union all select '2008-02-02' union all select '2008-02-03') b on a.day<=b.day
where not exists(select 1 from #t where col=a.col and day<=b.day and day>a.day)
order by a.col,b.day
/*
day col Status Val
---------- ---- ---------- ----------
2008-02-01 A No 100
2008-02-02 A Yes 100
2008-02-03 A Yes 120
2008-02-01 B No 20
2008-02-02 B No 20
2008-02-03 B Yes 100
2008-02-02 C YES 100
2008-02-03 C YES 100
2008-02-01 D NO 100
2008-02-02 D NO 100
2008-02-03 D NO 100 (11 row(s) affected)
*/
drop table #t
,[ConStatusYES]=sum(case Status when 'YES' then 1 else 0 end)
,[ConVal100]=sum(case when Status='YES' and Val=100 then 1 else 0 end)
from
(
select b.day,a.col,a.Status,a.Val
from #t a
right join (select [day]='2008-02-01' union all select '2008-02-02' union all select '2008-02-03') b
on a.day<=b.day
where not exists(select 1 from #t where col=a.col and day<=b.day and day>a.day) --order by col,day
) t
group by day
/*
DAY ConStatusYES ConVal100
---------- ------------ -----------
2008-02-01 0 0
2008-02-02 2 2
2008-02-03 3 2(3 row(s) affected)
*/