■附测试脚本:
CREATE TABLE [TABLE1] (
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CallCount] [int] NULL ,
[CallTime] [datetime] NULL CONSTRAINT [DF_TABLE1_CallTime] DEFAULT (getdate())
) ON [PRIMARY]
GOInsert into TABLE1 (name,CallCount,CallTime) values ('a',13,'2005-2-10 15:08:11')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',5,'2005-2-12 15:08:13')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',6,'2005-3-1 15:09:08')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',21,'2005-3-18 1:09:09')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',7,'2005-3-18 15:09:11')■取每天记录:
select Sum(CallCount) SumCount,convert(varchar(10),CallTime,120) day
from table1
where name='a'
group by convert(varchar(10),CallTime,120)
CREATE TABLE [TABLE1] (
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CallCount] [int] NULL ,
[CallTime] [datetime] NULL CONSTRAINT [DF_TABLE1_CallTime] DEFAULT (getdate())
) ON [PRIMARY]
GOInsert into TABLE1 (name,CallCount,CallTime) values ('a',13,'2005-2-10 15:08:11')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',5,'2005-2-12 15:08:13')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',6,'2005-3-1 15:09:08')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',21,'2005-3-18 1:09:09')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',7,'2005-3-18 15:09:11')■取每天记录:
select Sum(CallCount) SumCount,convert(varchar(10),CallTime,120) day
from table1
where name='a'
group by convert(varchar(10),CallTime,120)
解决方案 »
- 新手问题 数据源设置SQL Server连接失败
- 合并相邻行
- 如何在更新某个字段后通过触发器插入到另外的表?急...谢谢了
- 有点意思的 SQL 查询语句,请高手指教 谢谢
- 无法删除数据行问题,超郁闷,大家帮帮我吧
- 一个简单的数据更新问题 请高手来看看~~~
- SQL-Server 无法建立数据库关系图
- 想查找所有“日期”字段不为空的纪录,应该用什么SQL命令?
- 如何检测SQL是否安装以及是否正在运行,请各位谈谈自己的处理方法
- 將DBF挂到SQL Server 2000 裡做個鏈接服務器,速度很慢啊
- 求教:1、vb.6.0中如何打印数据表格啊? 2、如何用vb把sql数据库转换成excel和文件啊??
- 如何在有identity属性的字段上去除identity属性,只能用sql语句完成。急!!!!!!
CREATE TABLE [TABLE1] (
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CallCount] [int] NULL ,
[CallTime] [datetime] NULL CONSTRAINT [DF_TABLE1_CallTime] DEFAULT (getdate())
) ON [PRIMARY]
GOInsert into TABLE1 (name,CallCount,CallTime) values ('a',13,'2005-2-10 15:08:11')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',5,'2005-2-12 15:08:13')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',6,'2005-3-1 15:09:08')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',21,'2005-3-18 1:09:09')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',7,'2005-3-18 15:09:11')
go--■取每天记录:
declare @i int,@dt datetime
select @dt=min(CallTime),@i=datediff(day,@dt,max(CallTime))+1
from table1
where name='a'if @@rowcount=0 return
set rowcount @i
select id=identity(int,0,1) into #t from syscolumns a,syscolumns b
set rowcount 0select SumCount=isnull(a.SumCount,0),day=convert(varchar(10),@dt+b.id,120)
from(
select Sum(CallCount) SumCount,convert(varchar(10),CallTime,120) day
from table1
where name='a'
group by convert(varchar(10),CallTime,120)
)a right join #t b on datediff(day,@dt,a.day)=b.id
order by b.id
drop table #t
godrop table TABLE1
CREATE TABLE [TABLE1] (
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CallCount] [int] NULL ,
[CallTime] [datetime] NULL CONSTRAINT [DF_TABLE1_CallTime] DEFAULT (getdate())
) ON [PRIMARY]
GOInsert into TABLE1 (name,CallCount,CallTime) values ('a',13,'2005-2-10 15:08:11')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',5,'2005-2-12 15:08:13')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',6,'2005-3-1 15:09:08')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',21,'2005-3-18 1:09:09')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',7,'2005-3-18 15:09:11')
go--■取每天记录:
declare @i int,@dt datetime
select @dt=convert(char(8),min(CallTime),120)+'1'
,@i=datediff(day,@dt,convert(char(8),dateadd(month,1,max(CallTime)),120)+'1')
from table1
where name='a'if @@rowcount=0 return
set rowcount @i
select id=identity(int,0,1) into #t from syscolumns a,syscolumns b
set rowcount 0select SumCount=isnull(a.SumCount,0),day=convert(varchar(10),@dt+b.id,120)
from(
select Sum(CallCount) SumCount,convert(varchar(10),CallTime,120) day
from table1
where name='a'
group by convert(varchar(10),CallTime,120)
)a right join #t b on datediff(day,@dt,a.day)=b.id
order by b.id
drop table #t
godrop table TABLE1
goselect identity(int,1,1) as id into #t from sysobjects a,sysobjects bset rowcount 0
goselect
[day] = a.ndate,
SumCount = isnull(sum(CallCount),0)
from
(select dateadd(day,id,cast('2004-12-31' as datetime)) ndate from #t) a
left join
table1 b
on
datediff(day,a.ndate,b.CallTime) = 0 and b.name = 'a'
where
a.ndate between '2005-02-01' and '2005-03-31'
group by
a.ndate
order by
a.ndate
CREATE TABLE [TABLE1] (
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CallCount] [int] NULL ,
[CallTime] [datetime] NULL CONSTRAINT [DF_TABLE1_CallTime] DEFAULT (getdate())
) ON [PRIMARY]
GOInsert into TABLE1 (name,CallCount,CallTime) values ('a',13,'2005-2-10 15:08:11')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',5,'2005-2-12 15:08:13')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',6,'2005-3-1 15:09:08')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',21,'2005-3-18 1:09:09')
Insert into TABLE1 (name,CallCount,CallTime) values ('a',7,'2005-3-18 15:09:11')
go--■取每天记录:
declare @i int,@dt datetime
select @dt=min(CallTime),@i=datediff(day,@dt,max(CallTime))+1
from table1
where name='a'if @@rowcount=0 return
set rowcount @i
select id=identity(int,0,1) into #t from syscolumns a,syscolumns b
set rowcount 0select SumCount=isnull(a.SumCount,0),day=convert(varchar(10),@dt+b.id,120)
from(
select Sum(CallCount) SumCount,convert(varchar(10),CallTime,120) day
from table1
where name='a'
group by convert(varchar(10),CallTime,120)
)a right join #t b on datediff(day,@dt,a.day)=b.id
order by b.id
drop table #t
godrop table TABLE1
set @enddate='2005-03-31'
create table #1(ddate datetime)
while @begindate<=@enddate
begin
insert #1 values @begindate
set @begindate=@begindate+1
endselect name,sum(isnull(callcount,0)),ddate from #1 a left join 表 on day(day)=ddate group by name,ddate
set @enddate='2005-03-31'
create table #1(ddate datetime)
while @begindate<=@enddate
begin
insert #1 values @begindate
set @begindate=@begindate+1
endselect name,sum(isnull(callcount,0)),ddate from #1 a left join 表 on day(calltime)=ddate group by name,ddate
set @enddate='2005-03-31'
create table #1(ddate datetime)
while @begindate<=@enddate
begin
insert #1 values @begindate
set @begindate=@begindate+1
endselect name,sum(isnull(callcount,0)),ddate from #1 a left join 表 on day(calltime)=day(ddate) and month(calltime)=month(ddate) and year(calltime)=year(ddate) group by name,ddate
-----------
2004-01-01
2004-01-02
...
2005-04-01再关联select a.d day,sum(b.Callcount) Sumcount from tbl_date a left join 你的表 b on datediff(d,a.d,b.Calltime)=0
group by a.d