有这样一张表
想要如下效果:
我使用的是
select d.DateString,COUNT(Pass) as Pass,COUNT(Fail) as Fail from (select DateString ,case(Result) when 'Pass' then 'Pass' end as Pass ,case(Result) when 'Fail' then 'Fail' end as Fail from (select left(Convert(nvarchar, date,21),10) as DateString ,[Result] FROM [EFDemo].[dbo].[Result])table_tb)d group by d.DateString
事实上转换成字符串处理效率会比较低。各位大神,有没有一种方法 不需要转换日期的?
select datestring, count(Pass), count(Fail)
from(
select
convert(nvarchar, date,21) as datestring,
case Result when 'Pass' then 'Pass' end as Pass,
case Result when 'Fail' then 'Fail' end as Fail,
from 表
) a group by datestring
谢谢。
你这个效率已经很高了
谢谢。
datename?
谢谢。datename只能得到日期的一部分吧?
额,首先,感谢两位园友的热心回答。
经过一些思考,对上面的语句进行了一些优化。
1 select Convert(date, Date) as Date,COUNT(Pass) as Pass,COUNT(Fail) as Fail from 2 (select Date 3 ,case(Result) 4 when 'Pass' then 'Pass' end as Pass 5 ,case(Result) 6 when 'Fail' then 'Fail' end as Fail from [TestResult])d 7 group by Convert(date, Date)
这样写的好处有:1,效率稍微搞一些。
2,可以直接排序,不用使用charindex
再次感谢 @Rich.T,将转换放在内层确实好。