关于合并查询结果到一个表的问题

Question

先建个表：create table 你的表(列1 类型,列2 类型...)

把上面的语句，稍微修改一下：
 declare @day date
 set @day='2013-12-01'
 declare @day_2 date
 set @day_2='2013-12-08'
while(@day<@day_2)
begin--在这里insert
insert into 你的表(列1,列2,...)
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

Answer 1

按照你的说法declare @day date
 set @day='2013-12-01'
 declare @day_2 date
 set @day_2='2013-12-08'
while(@day<@day_2)
begin
create table #qwerty(date date,newaccount int,oneday int,twoday int,threeday int,fourday int,fiveday int,sixday int,sevenday int)
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
但是最后有错误

Answer 2

按照你的说法declare @day date
 set @day='2013-12-01'
 declare @day_2 date
 set @day_2='2013-12-08'
while(@day<@day_2)
begin
create table #qwerty(date date,newaccount int,oneday int,twoday int,threeday int,fourday int,fiveday int,sixday int,sevenday int)
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
但是最后有错误
create table #temp(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 #temp(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
select * from #temp成功了 谢谢大神教导了

Answer 3

按照你的说法declare @day date
 set @day='2013-12-01'
 declare @day_2 date
 set @day_2='2013-12-08'
while(@day<@day_2)
begin
create table #qwerty(date date,newaccount int,oneday int,twoday int,threeday int,fourday int,fiveday int,sixday int,sevenday int)
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
但是最后有错误
报的是什么错呢，能贴出来看看不

Answer 4

把你写的改了一下，临时表，在外面建，里面使用：
--外面建表,不要在里面建表，否则会报错
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