请教一个时间段的查询语句。

[wedding123]

生产设备运行记录表，如下：
id 时间 总量
M001 2013-10-1 00：30：00 238
M001 2013-10-1 01：30：00 252
M001 2013-10-1 02：30：00 276
M002 2013-10-1 00：30：00 1002
M002 2013-10-1 01：30：00 1230
M002 2013-10-1 02：30：00 1520
数据每天都自动生成的，值是生产个数总量累计，一小时记录一次，要查询
在一个时间段内，比如2013-10-1 到2013-11-20，设备在12:00:00到18:00:00这个期间生产个数超过600个的设备ID和日期，该怎么写这个查询？

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-03-07 13:27:44
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
--	Apr  2 2010 15:48:46 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([id] nvarchar(8),[时间] datetime,[总量] int)
insert [huang]
select 'M001','2013-10-01 00:30:00',238 union all
select 'M001','2013-10-01 01:30:00',252 union all
select 'M001','2013-10-01 02:30:00',276 union all
select 'M002','2013-10-01 00:30:00',1002 union all
select 'M002','2013-10-01 13:30:00',1230 union all
select 'M002','2013-10-01 17:30:00',1520
--------------生成数据--------------------------

select id,[时间]
from [huang]
WHERE CONVERT(VARCHAR(10),[时间],120) BETWEEN '2013-10-01' AND '2013-11-20'
AND CONVERT(VARCHAR(10),[时间],108) BETWEEN '12:00:00' AND '18:00:00'
GROUP BY id,[时间]
HAVING SUM([总量])>600

----------------结果----------------------------
/* 
id       时间
-------- -----------------------
M002     2013-10-01 13:30:00.000
M002     2013-10-01 17:30:00.000
*/

[發糞塗牆]

我把你最后两行的数据中时间改了一下，不然没有复合条件的数据

[wedding123]

不是SUM，要求是18点的总量减去12点的总量>600的

[發糞塗牆]

两个整点相减？

[wedding123]

要统计结果,比如： 设备ID 日期 生产量（12点到18点的） M001 2013-10-3 612 M002 2013-10-8 820

[t101lian]

select ID,日期 from 表 where  
CONVERT(VARCHAR(10),[时间],120) BETWEEN '2013-10-01' AND '2013-11-20'
AND CONVERT(VARCHAR(10),[时间],108) BETWEEN '12:00:00' AND '18:00:00' 
and 总量>600

[wedding123]

引用 4 楼 DBA_Huangzj 的回复:

两个整点相减？

是

[發糞塗牆]

if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([id] nvarchar(8),[时间] datetime,[总量] int)
insert [huang]
select 'M001','2013-10-01 00:30:00',238 union all
select 'M001','2013-10-01 01:30:00',252 union all
select 'M001','2013-10-01 02:30:00',276 union all
select 'M002','2013-10-01 12:00:00',1002 union all
select 'M002','2013-10-01 13:30:00',1230 union all
select 'M002','2013-10-01 18:00:00',1520
--------------生成数据--------------------------
 SELECT id,[时间],SUM([总量])[总量]
 FROM (
select id,[时间],[总量]
from [huang]
WHERE CONVERT(VARCHAR(10),[时间],120) BETWEEN '2013-10-01' AND '2013-11-20'
AND CONVERT(VARCHAR(10),[时间],108)='18:00:00'
UNION ALL 
select id,[时间],-1*[总量]
from [huang]
WHERE CONVERT(VARCHAR(10),[时间],120) BETWEEN '2013-10-01' AND '2013-11-20'
AND CONVERT(VARCHAR(10),[时间],108)='12:00:00'
)a
GROUP BY id,[时间]
HAVING SUM([总量])>600

/*
id       时间                      总量
-------- ----------------------- -----------
M002     2013-10-01 18:00:00.000 1520
*/

[victory610]

版主太给力啦。

[BzTech_123]

SELECT T.ID, T.DT, MX.TOTAL-MI.TOTAL FROM (SELECT [ID], CONVERT(VARCHAR(10), DATE_TIME, 120) AS DT FROM TB WHERE CONVERT(VARCHAR(10), DATE_TIME, 120) BETWEEN '2013-10-01' AND '2013-11-20' AND CONVERT(VARCHAR(10), DATE_TIME, 108) BETWEEN '12:00:00' AND '18:00:00' GROUP BY [ID], CONVERT(VARCHAR(10), DATE_TIME, 120)) T CROSS APPLY (SELECT TOP 1 TOTAL FROM TB WHERE CONVERT(VARCHAR(10), DATE_TIME,120)=T.DT AND CONVERT(VARCHAR(10), DATE_TIME, 108) BETWEEN '12:00:00' AND '18:00:00' ORDER BY DATE_TIME) MI CROSS APPLY (SELECT TOP 1 TOTAL FROM TB WHERE CONVERT(VARCHAR(10), DATE_TIME,120)=T.DT AND CONVERT(VARCHAR(10), DATE_TIME, 108) BETWEEN '12:00:00' AND '18:00:00' ORDER BY DATE_TIME DESC) MX WHERE MX.TOTAL-MI.TOTAL>600