这个语句怎么写？

[TSY20121101]

表的数据例子如下：
表：F
id countA updateTime
A1 20 2013-04-18 09:00:10
A1 30 2013-04-18 17:00:14
A1 107 2013-04-19 19:20:15
A1 24 2013-04-12 06:06:50
A2 72 2013-04-19 21:00:10

检索条件是 strID, strStartTime, strEndTime
其中strID对应id ； strStartTime和strEndTime对应updateTime
当 strID 为A1， strStartTime 2013-04-12 07:00:00, strEndTime 2013-04-20 00:00:00 时要检索出
A1 24 2013-04-12
A1 50 2013-04-18
A1 107 2013-04-19

其实就是根据3个检索条件，检索出每日的合计量
这用一条sql语句怎么写？ 直接用sql语句加检索变量，不是在存储过程里i

[Mr_Nice]

？？？

if object_id('[F]') is not null drop table [F]
go
create table [F] (id nvarchar(4),countA int,updateTime datetime)
insert into [F]
select 'A1',20,'2013-04-18 09:00:10' union all
select 'A1',30,'2013-04-18 17:00:14' union all
select 'A1',107,'2013-04-19 19:20:15' union all
select 'A1',24,'2013-04-12 06:06:50' union all
select 'A2',72,'2013-04-19 21:00:10'

select * from [F]


DECLARE @strid VARCHAR(10)
DECLARE @sdate DATETIME
DECLARE @edate DATETIME

SET @strid ='A1'
SET @sdate = '2013-04-12 07:00:00'
SET @edate = '2013-04-20 00:00:00'

SELECT *
FROM [F]
WHERE id =@strid AND updatetime BETWEEN @sdate AND @edate

/*
id	countA	updateTime
A1	20	2013-04-18 09:00:10.000
A1	30	2013-04-18 17:00:14.000
A1	107	2013-04-19 19:20:15.000*/

[TSY20121101]

引用 1 楼 OrchidCat 的回复:

？？？ SQL code?123456789101112131415161718192021222324252627282930if object_id('[F]') is not null drop table [F]gocreate table [F] (id nvarchar(4),countA int,updateTime datetime)insert i……

你没看懂我的帖子 我其实是要根据起始时间来统计当日的数量

[xiaotangJava]

create table #test (id nvarchar(4),countA int,updateTime datetime) insert into #test select 'A1',20,'2013-04-18 09:00:10' union all select 'A1',30,'2013-04-18 17:00:14' union all select 'A1',107,'2013-04-19 19:20:15' union all select 'A1',24,'2013-04-12 06:06:50' union all select 'A2',72,'2013-04-19 21:00:10' SELECT sum(countA) FROM #test WHERE id ='A1' AND updatetime BETWEEN '2013-04-12 07:00:00' AND '2013-04-20 00:00:00' GROUP BY CONVERT(VARCHAR(10),updateTime,120)

[qiuchu2003]

SELECT id,SUM(countA),CONVERT(VARCHAR(100), updateTime, 23) FROM F WHERE id=strID AND updateTime BETWEEN strStartTime AND strEndTime GROUP BY CONVERT(VARCHAR(100), updateTime, 23)

[xiaotangJava]

create table #test (id nvarchar(4),countA int,updateTime datetime)
insert into #test
select 'A1',20,'2013-04-18 09:00:10' union all
select 'A1',30,'2013-04-18 17:00:14' union all
select 'A1',107,'2013-04-19 19:20:15' union all
select 'A1',24,'2013-04-12 06:06:50' union all
select 'A2',72,'2013-04-19 21:00:10'
 
SELECT ID,SUM(countA)countA ,CONVERT(VARCHAR(10),updateTime,120)  FROM #test
WHERE id ='A1' AND updatetime BETWEEN '2013-04-12 06:06:50' AND '2013-04-20 00:00:00'
GROUP BY ID,CONVERT(VARCHAR(10),updateTime,120)

[TSY20121101]

引用 5 楼 xiaotangJava 的回复:

SQL code?1234567891011create table #test (id nvarchar(4),countA int,updateTime datetime)insert into #testselect 'A1',20,'2013-04-18 09:00:10' union allselect 'A1',30,'2013-04-18 17:00:14'……

不对, 根本没实现要求。

[TSY20121101]

上面几个贴sql语句的 都无法实现 统计当日总量的要求

[Mr_Nice]

DECLARE @strid VARCHAR(10)
DECLARE @sdate DATETIME
DECLARE @edate DATETIME

SET @strid ='A1'
SET @sdate = '2013-04-12 07:00:00'
SET @edate = '2013-04-20 00:00:00'

SELECT id,SUM(countA) AS scountA,CONVERT(VARCHAR(10),updatetime,120) AS updatetime
FROM (
SELECT *
FROM [F]
WHERE id =@strid AND updatetime BETWEEN @sdate AND @edate 
UNION ALL
SELECT TOP 1 * FROM [F]
WHERE id =@strid AND updatetime < @sdate
ORDER BY UPDATEtime DESC)T
GROUP BY id,CONVERT(VARCHAR(10),updatetime,120)
 
 
 /*
id	scountA	updatetime
A1	24	2013-04-12
A1	50	2013-04-18
A1	107	2013-04-19*/

[Mr_Nice]

如果是取消掉时间，仅作日期的比对，直接sum就可以获得了。

[xiaotangJava]

引用 7 楼 TSY20121101 的回复:

上面几个贴sql语句的 都无法实现 统计当日总量的要求

最后你要的结果不就是这样吗。。。 那里不能实现 A1 24 2013-04-12 A1 50 2013-04-18 A1 107 2013-04-19

[TSY20121101]

引用 10 楼 xiaotangJava 的回复:

引用 7 楼 TSY20121101 的回复:上面几个贴sql语句的 都无法实现 统计当日总量的要求 最后你要的结果不就是这样吗。。。 那里不能实现 A1 24 2013-04-12 A1 50 2013-04-18 A1 107 2013-04-19

不好意思，刚才我搞错了，结贴给分，分不多，大家分一下