IF object_id('tempdb..#TableA') is not null
drop table #TableA
select createdate,convert(varchar,createdate,23) as cdate
into #TableA from transactionlog
where CreateDate>=@date1+' 00:00:00' and createdate<=@date2+' 23:59:59'IF object_id('tempdb..#TableB') is not null
drop table #TableB
select cdate,
sum(1) as 当天所有交易记录
into #TableB from #TableA group by cdate order by cdate
以上感觉有点慢,要做两次,可否更快的方法呢?最好是一条语句搞定,谢谢!
drop table #TableA
select createdate,convert(varchar,createdate,23) as cdate
into #TableA from transactionlog
where CreateDate>=@date1+' 00:00:00' and createdate<=@date2+' 23:59:59'IF object_id('tempdb..#TableB') is not null
drop table #TableB
select cdate,
sum(1) as 当天所有交易记录
into #TableB from #TableA group by cdate order by cdate
以上感觉有点慢,要做两次,可否更快的方法呢?最好是一条语句搞定,谢谢!
from (
select createdate,convert(varchar,createdate,23) as cdate
from transactionlog
where CreateDate>=@date1+' 00:00:00' and createdate<=@date2+' 23:59:59')T
group by cdate order by cdate
提示group 附近有语法错误.
from (
select createdate,convert(varchar(10),createdate,23) as cdate
from transactionlog
where CreateDate>=@date1+' 00:00:00' and createdate<=@date2+' 23:59:59')T
group by cdate order by cdate
from (
select createdate,convert(varchar,createdate,23) as cdate
from transactionlog
where CreateDate>=@date1+' 00:00:00' and createdate<=@date2+' 23:59:59') as T
group by cdate order by cdate
但我发现,慢的原因就是其中的 convert(varchar,createdate,23) as cdate
它是将日期型转化为字符型。但我主要是为了按日期分组统计。不得不转呀! 唉。
大家有没有的建议呢? 望指点,谢谢!
from (
select createdate,year(createdate) yy,month(createdate) mm,day(createdate) dd
from transactionlog
where CreateDate>=@date1+' 00:00:00' and createdate<=@date2+' 23:59:59') as T
group by yy,mm,dd
order by yy,mm,dd
不对呢! group by yy ,dd,yy ,dd 仅是子查询中的。 外面这一层没有生成呢。 所以,出错!