2008-10-10 08:00:00.000 A01 3
2008-10-10 08:00:00.000 A02 4
2008-10-10 08:00:00.000 B01 30
2008-10-10 08:00:00.000 B02 40
2008-10-11 08:00:00.000 A01 5
2008-10-11 08:00:00.000 A02 6
2008-10-11 08:00:00.000 B01 50
2008-10-11 08:00:00.000 B02 60
DECLARE @CMName CHAR(10),@startDate DATETIME,@EndDate DATETIME
SET @CMName ='A01'
SET @startDate ='2008-01-28 9:53:49'
SET @EndDate='2008-12-30 10:09:24'--下面这段SQL 只能得到'A01'时的增量。
--我希望能同时得到'A02'的增量,即同时得到两个类别的增量。
SELECT CONVERT(CHAR(7),tDateTime,121) 时间,MAX(num)-MIN(num) 增量 FROM bxinlianxu
WHERE CMName=@CMName and CONVERT(CHAR(7),tDateTime,121) BETWEEN CONVERT(CHAR(7),@startDate,121) AND CONVERT(CHAR(7),@EndDate,121)
GROUP BY CONVERT(CHAR(7),tDateTime,121) 构建测试表。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bxinlianxu]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bxinlianxu]
GOCREATE TABLE [dbo].[bxinlianxu] (
[tDateTIme] [datetime] NULL ,
[cmName] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[num] [int] NULL
) ON [PRIMARY]
GOinsert blianxu(tDateTIme,cmName,num) values( '2008-10-10 08:00:00.000' , 'A01 ' , 3 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-10 08:00:00.000' , 'A02 ' , 4 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-10 08:00:00.000' , 'B01 ' , 30 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-10 08:00:00.000' , 'B02 ' , 40 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-11 08:00:00.000' , 'A01 ' , 5 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-11 08:00:00.000' , 'A02 ' , 6 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-11 08:00:00.000' , 'B01 ' , 50 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-11 08:00:00.000' , 'B02 ' , 60 )
2008-10-10 08:00:00.000 A02 4
2008-10-10 08:00:00.000 B01 30
2008-10-10 08:00:00.000 B02 40
2008-10-11 08:00:00.000 A01 5
2008-10-11 08:00:00.000 A02 6
2008-10-11 08:00:00.000 B01 50
2008-10-11 08:00:00.000 B02 60
DECLARE @CMName CHAR(10),@startDate DATETIME,@EndDate DATETIME
SET @CMName ='A01'
SET @startDate ='2008-01-28 9:53:49'
SET @EndDate='2008-12-30 10:09:24'--下面这段SQL 只能得到'A01'时的增量。
--我希望能同时得到'A02'的增量,即同时得到两个类别的增量。
SELECT CONVERT(CHAR(7),tDateTime,121) 时间,MAX(num)-MIN(num) 增量 FROM bxinlianxu
WHERE CMName=@CMName and CONVERT(CHAR(7),tDateTime,121) BETWEEN CONVERT(CHAR(7),@startDate,121) AND CONVERT(CHAR(7),@EndDate,121)
GROUP BY CONVERT(CHAR(7),tDateTime,121) 构建测试表。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bxinlianxu]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bxinlianxu]
GOCREATE TABLE [dbo].[bxinlianxu] (
[tDateTIme] [datetime] NULL ,
[cmName] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[num] [int] NULL
) ON [PRIMARY]
GOinsert blianxu(tDateTIme,cmName,num) values( '2008-10-10 08:00:00.000' , 'A01 ' , 3 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-10 08:00:00.000' , 'A02 ' , 4 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-10 08:00:00.000' , 'B01 ' , 30 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-10 08:00:00.000' , 'B02 ' , 40 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-11 08:00:00.000' , 'A01 ' , 5 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-11 08:00:00.000' , 'A02 ' , 6 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-11 08:00:00.000' , 'B01 ' , 50 )
insert blianxu(tDateTIme,cmName,num) values( '2008-10-11 08:00:00.000' , 'B02 ' , 60 )
SET @CMName ='(''A01'',''A02'')'
SET @startDate ='2008-01-28 9:53:49'
SET @EndDate='2008-12-30 10:09:24' exec('
SELECT CONVERT(CHAR(7),tDateTime,121) 时间,MAX(num)-MIN(num) 增量 FROM bxinlianxu
WHERE CMName in ' + @CMName + ' and CONVERT(CHAR(7),tDateTime,121) BETWEEN CONVERT(CHAR(7),@startDate,121) AND CONVERT(CHAR(7),@EndDate,121)
GROUP BY CONVERT(CHAR(7),tDateTime,121)
')
SET @CMName ='(''A01'',''A02'')'
SET @startDate ='2008-01-28 9:53:49'
SET @EndDate='2008-12-30 10:09:24' exec('
SELECT CONVERT(CHAR(7),tDateTime,121) 时间,MAX(num)-MIN(num) 增量 FROM bxinlianxu
WHERE CMName in ' + @CMName + ' and CONVERT(CHAR(7),tDateTime,121) BETWEEN ''' + CONVERT(CHAR(7),@startDate,121) + ''' AND ''' + CONVERT(CHAR(7),@EndDate,121) + '''
GROUP BY CONVERT(CHAR(7),tDateTime,121)
')