这个语句应该怎么写?

[abz87870]

tableA

Id Type Time7
1 0 2013-01-03 00:00:18
2 1 2013-01-05 01:20:07
3 2 2013-01-09 07:08:02
4 2 2013-01-03 07:07:02
5 2 2013-01-02 07:07:02


tableC
TypeId TypeName
0 AAA
1 BBB
2 CCC

tableA的Type字段对应tableC的TypeId字段


我想统计tableA中的Type字段为2从2013年1月3日到2013年1月9日的一个时间段(比如07:00:01-08:00:00)这一个小时之内的数据,当然这个只是具体的一个例子,实际情况是日期A到日期B是动态的,时间A到时间B也是动态的,请问这个语句应该怎么写?

[szm341]

declare @sdate varchar(10),@edate varchar(10)
declare @stime varchar(8),@etime varchar(8)
select @sdate='2013-01-03',@edate='2013-01-10',@stime='07:00:00',@etime='08:00:00'

select a.type,a.num,c.typename from 
(select Type,count(1)as num from tbA where time>@sdate and time<@edate 
and convert(varchar,getdate(),24) between @stime and @etime
group by Type)a inner join tbC c on a.type=c.typeid

[我腫了]

USE test
GO


-->生成表tableA

if object_id('tableA') is not null 
	drop table tableA
Go
Create table tableA([Id] smallint,[Type] smallint,[Time7] datetime)
Insert into tableA
Select 1,0,'2013-01-03 00:00:18'
Union all Select 2,1,'2013-01-05 01:20:07'
Union all Select 3,2,'2013-01-09 07:08:02'
Union all Select 4,2,'2013-01-03 07:07:02'
Union all Select 5,2,'2013-01-02 07:07:02'

-->生成表tableC

if object_id('tableC') is not null 
	drop table tableC
Go
Create table tableC([TypeId] smallint,[TypeName] nvarchar(3))
Insert into tableC
Select 0,N'AAA'
Union all Select 1,N'BBB'
Union all Select 2,N'CCC'

go




DECLARE 
	@startDate DATETIME
	,@endDate DATETIME
	,@startTime DATETIME
	,@endTime DATETIME
	,@Type SMALLINT
	

SELECT 
	@startDate='2013-01-03'
	,@endDate='2013-01-09'
	,@startTime='07:00:01'
	,@endTime='08:00:00'
	,@Type=2
	

SELECT 
		a.Id,a.Type,b.TypeName,a.Time7
	FROM tableA AS a,tableC AS b
	WHERE a.Type=b.TypeId
		AND Type=@Type
		AND Time7 >=@startDate AND Time7<@endDate+1		
		AND CONVERT(DATETIME,CONVERT(VARCHAR(9),Time7,108)) BETWEEN @startTime AND @endTime

/*
Id     Type   TypeName Time7
------ ------ -------- -----------------------
3      2      CCC      2013-01-09 07:08:02.000
4      2      CCC      2013-01-03 07:07:02.000
*/