希望有人能帮我优化下列代码 谢谢use MainDB
if exists (select * from db)
drop table dbcreate table db
(
ss varchar(500) not null
)
declare @str varchar(258)
declare @result varchar(1000)
set @str='a,b,c,d,e,f,g'
set @result =' insert into db(ss) select '''+replace(@str,',','''union select''')+''''
exec(@result) declare @a uniqueidentifierDeclare @icount int
set @icount=1
while @icount<=7
begin
;with cte as
(
select ss,row_number() over(order by ss)
as 'RowNumber' from db
)
select @a = (select ss from cte where RowNumber = @icount)
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/1/1 0:00:00' and '2009/1/31 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/2/1 0:00:00' and '2009/2/28 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/3/1 0:00:00' and '2009/3/31 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/4/1 0:00:00' and '2009/4/30 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/5/1 0:00:00' and '2009/5/31 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/6/1 0:00:00' and '2009/6/30 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/7/1 0:00:00' and '2009/7/31 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/8/1 0:00:00' and '2009/8/31 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/9/1 0:00:00' and '2009/9/30 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/10/1 0:00:00' and '2009/10/31 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/11/1 0:00:00' and '2009/11/30 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/12/1 0:00:00' and '2009/12/31 0:00:00' group by CID
set @icount = @icount +1
end
if exists (select * from db)
drop table dbcreate table db
(
ss varchar(500) not null
)
declare @str varchar(258)
declare @result varchar(1000)
set @str='a,b,c,d,e,f,g'
set @result =' insert into db(ss) select '''+replace(@str,',','''union select''')+''''
exec(@result) declare @a uniqueidentifierDeclare @icount int
set @icount=1
while @icount<=7
begin
;with cte as
(
select ss,row_number() over(order by ss)
as 'RowNumber' from db
)
select @a = (select ss from cte where RowNumber = @icount)
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/1/1 0:00:00' and '2009/1/31 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/2/1 0:00:00' and '2009/2/28 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/3/1 0:00:00' and '2009/3/31 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/4/1 0:00:00' and '2009/4/30 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/5/1 0:00:00' and '2009/5/31 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/6/1 0:00:00' and '2009/6/30 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/7/1 0:00:00' and '2009/7/31 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/8/1 0:00:00' and '2009/8/31 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/9/1 0:00:00' and '2009/9/30 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/10/1 0:00:00' and '2009/10/31 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/11/1 0:00:00' and '2009/11/30 0:00:00' group by CID
select CID,count(CID)from LogInfo where CID = @a and Date between '2009/12/1 0:00:00' and '2009/12/31 0:00:00' group by CID
set @icount = @icount +1
end
if exists (select * from db)
drop table db create table db
(
ss varchar(500) not null
)
declare @str varchar(258)
declare @result varchar(1000)
set @str='a,b,c,d,e,f,g'
set @result =' insert into db(ss) select '''+replace(@str,',','''union select''')+''''
exec(@result)
是创建表单 目的是为了做数组用
;with cte as
(
select ss,row_number() over(order by ss)
as 'RowNumber' from db
)
select @a = (select ss from cte where RowNumber = @icount)
就是选择表单的某行 内容 该表单 只有1个字段 就是前面生成的后面么 就是很容易看明白的 语句了