sql2000多表关联查询,在最后加1行汇总数?
sql2000多表关联查询,在最后加1行汇总数?我下面的好像可以,但总觉得好像没优化。--建立测试数据
--create table #tmp1(name varchar(20),indate datetime,inprice numeric(10,2))
--create table #tmp2(name varchar(20),outdate datetime,outprice numeric(10,2))
--insert into #tmp1(name ,indate,inprice) values('洗头水1',GETDATE(),50.10)
--insert into #tmp1(name ,indate,inprice) values('洗头水2',GETDATE(),60.10)
--insert into #tmp1(name ,indate,inprice) values('洗头水3',GETDATE(),60.00)
--insert into #tmp1(name ,indate,inprice) values('洗头水4',GETDATE(),70.00)
--insert into #tmp1(name ,indate,inprice) values('洗头水1',GETDATE(),52.10)
--insert into #tmp1(name ,indate,inprice) values('洗头水2',GETDATE(),63.10)
--insert into #tmp1(name ,indate,inprice) values('洗头水3',GETDATE(),65.00)
--insert into #tmp1(name ,indate,inprice) values('洗头水4',GETDATE(),75.00)
--insert into #tmp2(name ,outdate,outprice) values('洗头水1',GETDATE(),150.10)
--insert into #tmp2(name ,outdate,outprice) values('洗头水2',GETDATE(),120.10)
--insert into #tmp2(name ,outdate,outprice) values('洗头水3',GETDATE(),150.00)
--insert into #tmp2(name ,outdate,outprice) values('洗头水1',GETDATE(),152.10)
--insert into #tmp2(name ,outdate,outprice) values('洗头水2',GETDATE(),121.10)
--insert into #tmp2(name ,outdate,outprice) values('洗头水3',GETDATE(),152.00)
--查询
select t1.name as [名称],t1.indate as [入库日期],t1.inprice as [入库价格],t2.outdate as [出库日期],t2.outprice as [出库价格]
from #tmp1 t1
left join #tmp2 t2 on t2.name=t1.name
UNION ALL
select '汇总数' as [名称],getdate() as [入库日期],sum(a.入库价格) as [入库价格],getdate() as [出库日期],sum(a.出库价格) as [出库价格] from
(select t1.name as [名称],t1.indate as [入库日期],t1.inprice as [入库价格],t2.outdate as [出库日期],t2.outprice as [出库价格]
from #tmp1 t1
left join #tmp2 t2 on t2.name=t1.name) a