SQL Server:对不同表中的行求和

Question

表 1：

 item    Purchase price
 ------- -------------- 
 item1    10$
 item2    20$
 item3    30$

表 2：

 item    Sale Price
 ------- --------------
 item1    10$
 item2    20$
 item3    30$

我想要以下结果：

 row name         item1   item2   item3   total
 --------------  ------  ------  ------  ------
 Purchase price   10$     20$     30$     60$
 Sale price       15$     25$     35$     75$
 Profit           5$      5$      5$      15$

谢谢

Answer 1

您需要第三个表来汇总每个项目的购买和销售价格。然后，计算利润和总计并旋转行，使每个项目都成为一列。

检查下面的脚本(包括注释(，但请记住，对于大型表，可能需要一段时间才能获得结果。

--setup test variables
declare @table1 table(item varchar(50), purchase int)
declare @table2 table(item varchar(50), sale int)
insert into @table1 values ('item1', 10)
insert into @table1 values ('item2', 20)
insert into @table1 values ('item3', 30)
insert into @table2 values ('item1', 15)
insert into @table2 values ('item2', 25)
insert into @table2 values ('item3', 35)
--create a temporary table to hold each item's purchase price, sale price and profit
create table #sumTable (row varchar(50), item varchar(50), price int)
--insert purchase prices from table1
insert into #sumTable 
select 'purchase', item, purchase from @table1
--insert sale prices from table2
insert into #sumTable 
select 'sale', item, sale from @table2
--calculate profits for each item
insert into #sumTable
select 'profit', t1.item, sale - purchase
from @table1 t1
inner join @table2 t2 on t1.item = t2.item
--calculate totals
insert into #sumTable
select row, 'total', sum(price)
from #sumTable
group by row, item
--check what we have so far (uncomment if needed)
--select * from #sumTable
--roate the rows by row type, so that each item's name becomes a row
declare  @sql  nvarchar(max),
         @cols nvarchar(max)
select @cols = STUFF(
(select ',
SUM(CASE WHEN [item] = ''' + [item] + ''' THEN [price] ELSE NULL END) AS [' + [item] + ']'
from #sumTable
group by [item]
order by [item] 
for xml path(''),type).value('.','varchar(max)'), 1, 2, '')
set @sql = 'SELECT [row], ' + @cols + '  FROM #sumTable GROUP BY [row]'
execute(@sql)
--drop the temporary table
drop table #sumTable

结果如下所示

row         item1       item2       item3       total
----------- ----------- ----------- ----------- -------
profit      5           5           5           15
purchase    10          20          30          60
sale        15          25          35          75

Answer 2

您可以先pivot数据，将数据从行更改为列。

然后你union结果。

但是，这看起来像一个报告问题 - 因此使用报告工具(如 SQL Server 报告服务(可以更好地生成结果。