select a.时间,b.Con
from (select distinct CONVERT(varchar(10),a.时间,120) as 时间 from Orders) as a
cross apply(select COUNT(1) from orders where CONVERT(varchar(16),时间,120) between a.时间+' 22:00' and convert(varchar(10),dateadd(dd,1,a.时间),120)+' 07:00') as b (Con)
#4
sql server 2000好像没有cross apply吧?
#5
把今天 0 点到 7 点的数据,算到昨天吗 ?
#6
-- 把日时间减去 7 个小时,相当于把今天7 点前的数据移到了昨天
select convert(varchar(10),dateadd(hour,-7, crdate),121) , sum(id)
from sysobjects
-- 这里取原始数据的小时数,条件写 >= 22 or <= 7
where Datename(hour, crdate) >=22 or Datename(hour, crdate) <=7
group by convert(varchar(10),dateadd(hour, -7, crdate),121)
#7
SQL2000只能这样用
select a.时间,COUNT(b.时间) as con
from (select distinct CONVERT(varchar(10),a.时间,120) as 时间 from Orders) as a
left join orders as b on CONVERT(varchar(16),b.时间,120) between a.时间+' 22:00' and convert(varchar(10),dateadd(dd,1,a.时间),120)+' 07:00'
group by a.时间
#8
-- 把日时间减去 7 个小时,相当于把今天7 点前的数据移到了昨天
select convert(varchar(10),dateadd(hour,-7, crdate),121) , sum(id)
from sysobjects
-- 这里取原始数据的小时数,条件写 >= 22 or <= 7
where Datename(hour, crdate) >=22 or Datename(hour, crdate) <=7
group by convert(varchar(10),dateadd(hour, -7, crdate),121)
select a.时间,b.Con
from (select distinct CONVERT(varchar(10),a.时间,120) as 时间 from Orders) as a
cross apply(select COUNT(1) from orders where CONVERT(varchar(16),时间,120) between a.时间+' 22:00' and convert(varchar(10),dateadd(dd,1,a.时间),120)+' 07:00') as b (Con)
sql server 2000好像没有cross apply吧?
SQL2000只能这样用
select a.时间,COUNT(b.时间) as con
from (select distinct CONVERT(varchar(10),a.时间,120) as 时间 from Orders) as a
left join orders as b on CONVERT(varchar(16),b.时间,120) between a.时间+' 22:00' and convert(varchar(10),dateadd(dd,1,a.时间),120)+' 07:00'
group by a.时间
select a.时间,COUNT(b.时间) as con
from (select distinct CONVERT(varchar(10),a.时间,120) as 时间 from Orders) as a
left join orders as b on
b.时间 between a.时间+' 22:00' and convert(varchar(10),dateadd(dd,1,a.时间),120)+' 07:00'
group by a.时间
改改在时间列建上索引再测效率
#11
-- 把日时间减去 7 个小时,相当于把今天7 点前的数据移到了昨天
select convert(varchar(10),dateadd(hour,-7, crdate),121) , sum(id)
from sysobjects
-- 这里取原始数据的小时数,条件写 >= 22 or <= 7
where Datename(hour, crdate) >=22 or Datename(hour, crdate) <=7
group by convert(varchar(10),dateadd(hour, -7, crdate),121)
select a.时间,b.Con
from (select distinct CONVERT(varchar(10),a.时间,120) as 时间 from Orders) as a
cross apply(select COUNT(1) from orders where CONVERT(varchar(16),时间,120) between a.时间+' 22:00' and convert(varchar(10),dateadd(dd,1,a.时间),120)+' 07:00') as b (Con)
select a.时间,b.Con
from (select distinct CONVERT(varchar(10),a.时间,120) as 时间 from Orders) as a
cross apply(select COUNT(1) from orders where CONVERT(varchar(16),时间,120) between a.时间+' 22:00' and convert(varchar(10),dateadd(dd,1,a.时间),120)+' 07:00') as b (Con)
sql server 2000好像没有cross apply吧?
#5
把今天 0 点到 7 点的数据,算到昨天吗 ?
#6
-- 把日时间减去 7 个小时,相当于把今天7 点前的数据移到了昨天
select convert(varchar(10),dateadd(hour,-7, crdate),121) , sum(id)
from sysobjects
-- 这里取原始数据的小时数,条件写 >= 22 or <= 7
where Datename(hour, crdate) >=22 or Datename(hour, crdate) <=7
group by convert(varchar(10),dateadd(hour, -7, crdate),121)
select a.时间,b.Con
from (select distinct CONVERT(varchar(10),a.时间,120) as 时间 from Orders) as a
cross apply(select COUNT(1) from orders where CONVERT(varchar(16),时间,120) between a.时间+' 22:00' and convert(varchar(10),dateadd(dd,1,a.时间),120)+' 07:00') as b (Con)
sql server 2000好像没有cross apply吧?
SQL2000只能这样用
select a.时间,COUNT(b.时间) as con
from (select distinct CONVERT(varchar(10),a.时间,120) as 时间 from Orders) as a
left join orders as b on CONVERT(varchar(16),b.时间,120) between a.时间+' 22:00' and convert(varchar(10),dateadd(dd,1,a.时间),120)+' 07:00'
group by a.时间
#8
-- 把日时间减去 7 个小时,相当于把今天7 点前的数据移到了昨天
select convert(varchar(10),dateadd(hour,-7, crdate),121) , sum(id)
from sysobjects
-- 这里取原始数据的小时数,条件写 >= 22 or <= 7
where Datename(hour, crdate) >=22 or Datename(hour, crdate) <=7
group by convert(varchar(10),dateadd(hour, -7, crdate),121)
select a.时间,b.Con
from (select distinct CONVERT(varchar(10),a.时间,120) as 时间 from Orders) as a
cross apply(select COUNT(1) from orders where CONVERT(varchar(16),时间,120) between a.时间+' 22:00' and convert(varchar(10),dateadd(dd,1,a.时间),120)+' 07:00') as b (Con)
sql server 2000好像没有cross apply吧?
SQL2000只能这样用
select a.时间,COUNT(b.时间) as con
from (select distinct CONVERT(varchar(10),a.时间,120) as 时间 from Orders) as a
left join orders as b on CONVERT(varchar(16),b.时间,120) between a.时间+' 22:00' and convert(varchar(10),dateadd(dd,1,a.时间),120)+' 07:00'
group by a.时间
select a.时间,COUNT(b.时间) as con
from (select distinct CONVERT(varchar(10),a.时间,120) as 时间 from Orders) as a
left join orders as b on
b.时间 between a.时间+' 22:00' and convert(varchar(10),dateadd(dd,1,a.时间),120)+' 07:00'
group by a.时间
改改在时间列建上索引再测效率
#11
-- 把日时间减去 7 个小时,相当于把今天7 点前的数据移到了昨天
select convert(varchar(10),dateadd(hour,-7, crdate),121) , sum(id)
from sysobjects
-- 这里取原始数据的小时数,条件写 >= 22 or <= 7
where Datename(hour, crdate) >=22 or Datename(hour, crdate) <=7
group by convert(varchar(10),dateadd(hour, -7, crdate),121)