full house

今天在修改一个项目的时候要实现一功能：将系统中图表中的数据每天存档起来，以备查看。
一开始我用想用表变量来实现，先将数据整理到表变量中，然后插入到相应的存档数据库中。

Declare @chart table(room int,qty1 int,qty3 int)
//-------------------
insert into @chart
Select a.room,isnull(b.Qty1,0) qty1,isnull(d.Qty3,0) qty3
from t_room_all a
left join ( Select Room, sum(convert(int,OrderQty))-Sum(PrintedQty) Qty1
from t_pop_all where Status=0 or Status=1 group by Room) b on a.room = b.room
left join ( Select Room, sum(convert(int,OrderQty))-Sum(PrintedQty) Qty2
from t_pop_all where Status=1 group by Room) c
on a.room = c.room left join ( Select Room, sum(convert(int,ProducedQty))-Sum(PrintedQty) Qty3
from t_pop_all where Status=0 or Status=1 group by Room ) d
on a.room = d.room
//--------------------
Insert into t_chart_history (room,qty1,qty3) select * from @chart

不过后来我再整理了一下，直接用一句SQL就可以了，不用表变量了。

Insert into t_chart_history (room,qty1,qty3)
Select a.room,isnull(b.Qty1,0) qty1,isnull(d.Qty3,0) qty3
from t_room_all a
left join ( Select Room, sum(convert(int,OrderQty))-Sum(PrintedQty) Qty1
from t_pop_all where Status=0 or Status=1 group by Room) b on a.room = b.room
left join ( Select Room, sum(convert(int,OrderQty))-Sum(PrintedQty) Qty2
from t_pop_all where Status=1 group by Room) c
on a.room = c.room left join ( Select Room, sum(convert(int,ProducedQty))-Sum(PrintedQty) Qty3
from t_pop_all where Status=0 or Status=1 group by Room ) d
on a.room = d.room

这个SQL语句比较复杂，一般采用分段写，最后整合成一个长句。