关于合并查询结果到一个表的问题
现在有一个查询留存的SQL语句
declare @day date
set @day='2013-12-01'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
begin
select @day as date,COUNT(account) as newaccount,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,1,@day)and logincount>1))as'1day' ,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,2,@day)and logincount>1))as'2day' ,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,3,@day)and logincount>1))as'3day' ,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,4,@day)and logincount>1))as'4day' ,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,5,@day)and logincount>1))as'5day' ,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,6,@day)and logincount>1))as'6day' ,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,7,@day)and logincount>1))as'7day'
from [12_TS_accountlogin_log]where logincount=1 and (convert(date,logtime ,105))=@day
set @day=dateadd(dd,1,@day)
end
--外面建表,不要在里面建表,否则会报错
if OBJECT_ID('tempdb..#qwerty') is not null
drop table #qwerty
create table #qwerty(date date,newaccount int,oneday int,twoday int,threeday int,fourday int,fiveday int,sixday int,sevenday int)
declare @day date
set @day='2013-12-01'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
begin
insert into #qwerty(date,newaccount,oneday,twoday,threeday,fourday,fiveday,sixday,sevenday)
select @day as date,COUNT(account) as newaccount,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,1,@day)and logincount>1))as'1day' ,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,2,@day)and logincount>1))as'2day' ,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,3,@day)and logincount>1))as'3day' ,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,4,@day)and logincount>1))as'4day' ,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,5,@day)and logincount>1))as'5day' ,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,6,@day)and logincount>1))as'6day' ,
(select COUNT(a.account)from(select*from[12_TS_accountlogin_log]where logincount=1 and(convert(date,logtime,105))=@day)a where account in(select account from[12_TS_accountlogin_log]where(convert(date,logtime,105))=dateadd(dd,7,@day)and logincount>1))as'7day'
from [12_TS_accountlogin_log]where logincount=1 and (convert(date,logtime ,105))=@day
set @day=dateadd(dd,1,@day)
end