大家好!
问题:将视图的查询结果,按每行插入到相对应的表里。大家看看都有哪些方法实现。视图语句如下:
select
username,
sum(case when datediff(dd,date,getdate())=0 and Stat='in' then 1 else 0 end) as 'in',
sum(case when datediff(dd,date,getdate())=0 and Stat='into' then 1 else 0 end) as 'into',
sum(case when datediff(dd,date,getdate())=0 and Stat='ou' then 1 else 0 end) as 'ou'
from
View_InOut
group by
username 视图查询结果如下:
username in into ou
51job 0 0 2
sina 3 0 1
SouJing 0 6 1最终目标:
51job 0 0 2 这行数据插入到已经存在的Site_Day_51job表里
sina 3 0 1 这行数据插入到已经存在的Site_Day__sina表里
SouJing 0 6 1 这行数据插入到已经存在的Site_Day__SouJing表里不好意思,没注意分数竟然用的差不多了,下次一定补多些。问题还是希望各位解达。多谢!
问题:将视图的查询结果,按每行插入到相对应的表里。大家看看都有哪些方法实现。视图语句如下:
select
username,
sum(case when datediff(dd,date,getdate())=0 and Stat='in' then 1 else 0 end) as 'in',
sum(case when datediff(dd,date,getdate())=0 and Stat='into' then 1 else 0 end) as 'into',
sum(case when datediff(dd,date,getdate())=0 and Stat='ou' then 1 else 0 end) as 'ou'
from
View_InOut
group by
username 视图查询结果如下:
username in into ou
51job 0 0 2
sina 3 0 1
SouJing 0 6 1最终目标:
51job 0 0 2 这行数据插入到已经存在的Site_Day_51job表里
sina 3 0 1 这行数据插入到已经存在的Site_Day__sina表里
SouJing 0 6 1 这行数据插入到已经存在的Site_Day__SouJing表里不好意思,没注意分数竟然用的差不多了,下次一定补多些。问题还是希望各位解达。多谢!
insert Site_Day_51job
select
username,
sum(case when datediff(dd,date,getdate())=0 and Stat='in' then 1 else 0 end) as 'in',
sum(case when datediff(dd,date,getdate())=0 and Stat='into' then 1 else 0 end) as 'into',
sum(case when datediff(dd,date,getdate())=0 and Stat='ou' then 1 else 0 end) as 'ou'
from
View_InOut
where username='51job'
group by
username
以下類似
然后用3条语句
insert into
Site_Day_51job(username,in,into,ou)
select
username,in,into,ou from view
where
id=1
username in into ou
51job 0 0 2
sina 3 0 1
SouJing 0 6 1
create proc testasDeclare @Sql varchar(1000)
select @Sql=
'insert into Site_Day_'+username +
' select '''+
cast(sum(case when datediff(dd,date,getdate())=0 and Stat='in' then 1 else 0 end) as varchar)+''','+
''''+cast(sum(case when datediff(dd,date,getdate())=0 and Stat='into' then 1 else 0 end) as varchar)+''','+
''''+cast(sum(case when datediff(dd,date,getdate())=0 and Stat='ou' then 1 else 0 end) as varchar)+''''from
View_InOut
group by
username exec(@Sql)
date sin in ou
create proc testasDeclare @Sql varchar(1000)
select @Sql=
'insert into Site_Day_'+username +
' select '''+ username+''','+
''''+cast(sum(case when datediff(dd,date,getdate())=0 and Stat='in' then 1 else 0 end) as varchar)+''','+
''''+cast(sum(case when datediff(dd,date,getdate())=0 and Stat='into' then 1 else 0 end) as varchar)+''','+
''''+cast(sum(case when datediff(dd,date,getdate())=0 and Stat='ou' then 1 else 0 end) as varchar)+''''from
View_InOut
group by
username exec(@Sql)
少了username