declare @cc int set @cc=3 declare @sql varchar(1000) select @sql=' SELECT TOP 1 istr FROM uf_splitstring(''aa,bb,cc,dd,ee,ff,gg,hh,kk'','','') WHERE (ID NOT IN (SELECT TOP '+rtrim(@cc)+' id FROM uf_splitstring(''aa,bb,cc,dd,ee,ff,gg,hh,kk'','','') ORDER BY id)) ORDER BY id ' exec(@sql)
declare @cc int, @str varchar(1000) set @cc=3 select @str='SELECT TOP 1 istr FROM uf_splitstring(''aa,bb,cc,dd,ee,ff,gg,hh,kk'','','') WHERE (ID NOT IN (SELECT TOP '+@cc+' id FROM uf_splitstring(''aa,bb,cc,dd,ee,ff,gg,hh,kk'','','') ORDER BY id)) ORDER BY id ' ---这样试试
这是我得存储过程/* CREATE PROCEDURE usp_QueryRawData1 @start varchar(50), --开始日期 @end varchar(50), --结束日期 @tagIDs varchar(3000), --需要检索的点集合 @recordPerPage int, --每页的记录行数 @timespan int AS */ --/* declare @start varchar(50) --开始日期 declare @end varchar(50) --结束日期 declare @tagIDs varchar(3000) --需要检索的点集合 declare @recordPerPage int declare @timespan int
set @start='2007-3-5 10:00:00' set @end = '2007-3-5 18:40:00' set @tagIDs='16842754, 16842758' set @recordPerPage=10 set @timespan=20 --*/ -- 指定表名、列名 declare @lTable char(50) declare @lColTag char(50) declare @lColPrimary char(50)set @lTable = '#t' set @lColTag = 'TagId' set @lColPrimary = 'ID'declare @tempTable char(50) declare @tempColTag char(50) declare @tempColPrimary char(50)set @tempTable = '#ttt' set @tempColTag = 'TagId' set @tempColPrimary = 'ID'-- 定义局部变量,用来构造SQL语句 declare @strSQL char(8000) --定义SQL语句,获取指定的页的纪录 declare @strTagNameSQL char(8000) --定义SQL语句,获取指定的页的纪录 declare @strValueSQL char(8000) --定义SQL语句,获取指定的页的纪录 declare @strWhere varchar(3900) --定义条件语句 --条件语句--------------------------beginset @strWhere = ''-- 点集合 if(ltrim(rtrim(@tagIDs))<>'') set @strWhere = ltrim(@strWhere) + cast(@lColTag as varchar(20)) +' in(' + ltrim(rtrim(cast(@tagIDs as varchar(3800))))+ ')'--排序方式 --if(ltrim(rtrim( @sort))=0) --set @sort = 1 --条件语句--------------------------end--定义临时表,保存符合检索条件的多个表的纪录----------------begincreate table #t ( ID int, TagId int, IOValue varchar(32), [TimeStamp] datetime, Quality smallint, SamplingMode tinyint )--insert into #t --exec usp_RecordsFromTables @start,@end --定义临时表,保存符合检索条件的多个表的纪录----------------end -- SQL-- 判断条件是否为空 if(ltrim(rtrim(@strWhere))<>'') set @strSQL = 'select * from '+ cast(@lTable as varchar(20)) +' where ' + ltrim(rtrim(cast(@strWhere as varchar(3900)))) else set @strSQL = 'select * from '+ cast(@lTable as varchar(20)) -- 执行SQL语句,返回结果集create table #tt ( ID int, TagId int, IOValue varchar(32), [TimeStamp] datetime, Quality smallint, SamplingMode tinyint )--insert into #tt --exec(@strSQL)create table #ttt ( ID int, TagId int, IOValue varchar(32), [TimeStamp] datetime, Quality smallint, SamplingMode tinyint )--为了循环执行每个点,建立临时表存放点TagID----------------begin declare @tagCount int ---存放用户传入点的数量 declare @i int ---存放循环变量 declare @tagOne varchar(10) ---存放每个点的ID declare @TempStart varchar(50) --开始日期 declare @TempEnd varchar(50) --结束日期select @tagCount=count(*) from uf_splitstring(@tagIDs,',')----取点的数量 set @i = 0 while @i<@tagCount ----依次循环取每个点 begin declare @mysql varchar(1000) declare @tepmtagIDs varchar(200)
--select top 1 @tagOne=istr from uf_splitstring(@tagIDs,',') where (id not in(select top 2 id from uf_splitstring(@tagIDs,',') order by id)) order by id
set @mysql='SELECT TOP 1 istr FROM uf_splitstring('+@tagIDs+','+'',''+')WHERE (ID NOT IN (SELECT TOP '+@i+' id FROM uf_splitstring('+@tagIDs+','+'',''+') ORDER BY id)) ORDER BY id ' exec(@mysql) while(cast(@start as datetime)<cast(@end as datetime)) begin set @TempEnd = DATEADD(minute,@timespan,cast(@start as datetime)) insert into #t exec usp_RecordsFromTables @start,@TempEnd insert into #tt exec(@strSQL) insert into #ttt select top 1 * from #tt where [TimeStamp] >=@start and [TimeStamp]<DATEADD(minute,@timespan,cast(@start as datetime)) and TagId = ltrim(rtrim(@tagIDs)) set @start = DATEADD(minute,@timespan,cast(@start as datetime)) end set @i=@i+1 end---------------------------------------------------------------------------------------------------end set @strValueSQL = 'select case when (count(*)<'+cast(@recordPerPage as varchar(50))+')' + ' and (count(*)!=0)' + ' then ''1'' else CEILING(cast(count(*) as decimal)/'+cast(@recordPerPage as varchar(50))+') end ' + ' as count from '+ cast(@tempTable as varchar(20)) -- 执行SQL语句,返回结果集 exec(@strValueSQL) GO
set @cc=3
declare @sql varchar(1000)
select @sql='
SELECT TOP 1 istr FROM uf_splitstring(''aa,bb,cc,dd,ee,ff,gg,hh,kk'','','')
WHERE (ID NOT IN
(SELECT TOP '+rtrim(@cc)+' id FROM uf_splitstring(''aa,bb,cc,dd,ee,ff,gg,hh,kk'','','')
ORDER BY id))
ORDER BY id '
exec(@sql)
@str varchar(1000)
set @cc=3
select @str='SELECT TOP 1 istr FROM uf_splitstring(''aa,bb,cc,dd,ee,ff,gg,hh,kk'','','') WHERE (ID NOT IN (SELECT TOP '+@cc+' id FROM uf_splitstring(''aa,bb,cc,dd,ee,ff,gg,hh,kk'','','') ORDER BY id)) ORDER BY id '
---这样试试
用游标逐行取.如何去啊,将每次取道的字段值放在一个变量中
CREATE PROCEDURE usp_QueryRawData1
@start varchar(50), --开始日期
@end varchar(50), --结束日期
@tagIDs varchar(3000), --需要检索的点集合
@recordPerPage int, --每页的记录行数
@timespan int
AS
*/
--/*
declare @start varchar(50) --开始日期
declare @end varchar(50) --结束日期
declare @tagIDs varchar(3000) --需要检索的点集合
declare @recordPerPage int
declare @timespan int
set @start='2007-3-5 10:00:00'
set @end = '2007-3-5 18:40:00'
set @tagIDs='16842754, 16842758'
set @recordPerPage=10
set @timespan=20
--*/
-- 指定表名、列名
declare @lTable char(50)
declare @lColTag char(50)
declare @lColPrimary char(50)set @lTable = '#t'
set @lColTag = 'TagId'
set @lColPrimary = 'ID'declare @tempTable char(50)
declare @tempColTag char(50)
declare @tempColPrimary char(50)set @tempTable = '#ttt'
set @tempColTag = 'TagId'
set @tempColPrimary = 'ID'-- 定义局部变量,用来构造SQL语句
declare @strSQL char(8000) --定义SQL语句,获取指定的页的纪录
declare @strTagNameSQL char(8000) --定义SQL语句,获取指定的页的纪录
declare @strValueSQL char(8000) --定义SQL语句,获取指定的页的纪录
declare @strWhere varchar(3900) --定义条件语句
--条件语句--------------------------beginset @strWhere = ''-- 点集合
if(ltrim(rtrim(@tagIDs))<>'')
set @strWhere = ltrim(@strWhere) + cast(@lColTag as varchar(20)) +' in(' + ltrim(rtrim(cast(@tagIDs as varchar(3800))))+ ')'--排序方式
--if(ltrim(rtrim( @sort))=0)
--set @sort = 1
--条件语句--------------------------end--定义临时表,保存符合检索条件的多个表的纪录----------------begincreate table #t
(
ID int,
TagId int,
IOValue varchar(32),
[TimeStamp] datetime,
Quality smallint,
SamplingMode tinyint
)--insert into #t
--exec usp_RecordsFromTables @start,@end
--定义临时表,保存符合检索条件的多个表的纪录----------------end
-- SQL-- 判断条件是否为空
if(ltrim(rtrim(@strWhere))<>'')
set @strSQL = 'select * from '+ cast(@lTable as varchar(20)) +' where ' + ltrim(rtrim(cast(@strWhere as varchar(3900))))
else
set @strSQL = 'select * from '+ cast(@lTable as varchar(20))
-- 执行SQL语句,返回结果集create table #tt
(
ID int,
TagId int,
IOValue varchar(32),
[TimeStamp] datetime,
Quality smallint,
SamplingMode tinyint
)--insert into #tt
--exec(@strSQL)create table #ttt
(
ID int,
TagId int,
IOValue varchar(32),
[TimeStamp] datetime,
Quality smallint,
SamplingMode tinyint
)--为了循环执行每个点,建立临时表存放点TagID----------------begin
declare @tagCount int ---存放用户传入点的数量
declare @i int ---存放循环变量
declare @tagOne varchar(10) ---存放每个点的ID
declare @TempStart varchar(50) --开始日期
declare @TempEnd varchar(50) --结束日期select @tagCount=count(*) from uf_splitstring(@tagIDs,',')----取点的数量
set @i = 0
while @i<@tagCount ----依次循环取每个点
begin
declare @mysql varchar(1000)
declare @tepmtagIDs varchar(200)
--select top 1 @tagOne=istr from uf_splitstring(@tagIDs,',') where (id not in(select top 2 id from uf_splitstring(@tagIDs,',') order by id)) order by id
set @mysql='SELECT TOP 1 istr FROM uf_splitstring('+@tagIDs+','+'',''+')WHERE (ID NOT IN (SELECT TOP '+@i+' id FROM uf_splitstring('+@tagIDs+','+'',''+') ORDER BY id)) ORDER BY id '
exec(@mysql)
while(cast(@start as datetime)<cast(@end as datetime))
begin
set @TempEnd = DATEADD(minute,@timespan,cast(@start as datetime))
insert into #t
exec usp_RecordsFromTables @start,@TempEnd
insert into #tt
exec(@strSQL)
insert into #ttt
select top 1 * from #tt where [TimeStamp] >=@start and [TimeStamp]<DATEADD(minute,@timespan,cast(@start as datetime)) and TagId = ltrim(rtrim(@tagIDs))
set @start = DATEADD(minute,@timespan,cast(@start as datetime))
end
set @i=@i+1
end---------------------------------------------------------------------------------------------------end
set @strValueSQL = 'select case when (count(*)<'+cast(@recordPerPage as varchar(50))+')'
+ ' and (count(*)!=0)'
+ ' then ''1'' else CEILING(cast(count(*) as decimal)/'+cast(@recordPerPage as varchar(50))+') end '
+ ' as count from '+ cast(@tempTable as varchar(20)) -- 执行SQL语句,返回结果集
exec(@strValueSQL)
GO