假设有2个表 进货表buy和出货表sale 数据如下
buy
进货编码 名称 进货箱数 单价
id name count price
1 啤酒 100 50
2 啤酒 200 55
3 白酒 100 200
4 白酒 200 220sale:
出货编码 名称 出货箱数 单价
id name count price
1 啤酒 80 55
2 啤酒 150 60
3 白酒 80 250
4 白酒 150 300
问如何得到下面的结果
啤酒进货总金额 啤酒出货总金额 白酒进货总金额 白酒进货总金额
numbuyBeer numsaleBeer numbuyWine numsaleWine
buy
进货编码 名称 进货箱数 单价
id name count price
1 啤酒 100 50
2 啤酒 200 55
3 白酒 100 200
4 白酒 200 220sale:
出货编码 名称 出货箱数 单价
id name count price
1 啤酒 80 55
2 啤酒 150 60
3 白酒 80 250
4 白酒 150 300
问如何得到下面的结果
啤酒进货总金额 啤酒出货总金额 白酒进货总金额 白酒进货总金额
numbuyBeer numsaleBeer numbuyWine numsaleWine
啤酒进货总金额 , 啤酒出货总金额, 白酒进货总金额 , 白酒进货总金额 from
(select
啤酒进货总金额=sum(case when name='啤酒' then [count]*price else 0 end),
白酒进货总金额=sum(case when name='白酒' then [count]*price else 0 end)
from
buy)T1,
(select
啤酒出货总金额=sum(case when name='啤酒' then [count]*price else 0 end),
白酒进货总金额=sum(case when name='白酒' then [count]*price else 0 end)
from
sales)T2
insert into @buy select 1,'啤酒',100,50
insert into @buy select 2,'啤酒',200,55
insert into @buy select 3,'白酒',100,200
insert into @buy select 4,'白酒',200,220declare @sale table (id int,name varchar(10),[count] int,price int)
insert into @sale select 1,'啤酒',80,50
insert into @sale select 2,'啤酒',150,60
insert into @sale select 3,'白酒',80,250
insert into @sale select 4,'白酒',150,300select
sum(case when type=1 and name='啤酒' then price end) as '啤酒进货总金额',
sum(case when type=2 and name='啤酒' then price end) as '啤酒出货总金额',
sum(case when type=1 and name='白酒' then price end) as '白酒进货总金额',
sum(case when type=2 and name='白酒' then price end) as '白酒进货总金额'
from
(
select *,type=1 from @buy
union all
select *,type=2 from @sale
)tp105 110 420 550
insert buy select 1 , '啤酒' , 100 , 50
insert buy select 2 , '啤酒' , 200 , 55
insert buy select 3 , '白酒' , 100 , 200
insert buy select 4 , '白酒' , 200 , 220create table sale(出货编码 int,名称 varchar(20),出货箱数 int,单价 int)
insert sale select 1, '啤酒' , 80 , 55
insert sale select 2 , '啤酒' , 150 , 60
insert sale select 3 , '白酒' , 80 , 250
insert sale select 4 , '白酒', 150 , 300select 啤酒进货总金额=max(case when a.名称='啤酒' then a.总金额 else 0 end)
,啤酒出货总金额=max(case when b.名称='啤酒' then b.总金额 else 0 end)
,白酒进货总金额=max(case when a.名称='白酒' then a.总金额 else 0 end)
,白酒出货总金额=max(case when b.名称='白酒' then b.总金额 else 0 end)
from (select 名称,总金额=sum(进货箱数*单价) from buy group by 名称) a
inner join (select 名称,总金额=sum(出货箱数*单价) from sale group by 名称) b
on a.名称=b.名称drop table buy,sale/*
啤酒进货总金额 啤酒出货总金额 白酒进货总金额 白酒出货总金额
----------- ----------- ----------- -----------
16000 13400 64000 65000(所影响的行数为 1 行)
*/
go
insert into buy select 1,'啤酒',100,50
union all
select 2,'啤酒',200,55
union all
select 3,'白酒',100,200
union all
select 4,'白酒',200,220create table sale([id] int,[name] varchar(10),[count] int,price numeric(10,2))
go
insert into sale select 1,'啤酒',80,55
union all
select 2,'啤酒',150,60
union all
select 3,'白酒',80,250
union all
select 4,'白酒',150,300select
t1.啤酒进货总金额 , T2.啤酒出货总金额, t1.白酒进货总金额 , T2.白酒出货总金额 from
(select
啤酒进货总金额=sum(case when name='啤酒' then [count]*price else 0 end),
白酒进货总金额=sum(case when name='白酒' then [count]*price else 0 end)
from
buy)T1,
(select
啤酒出货总金额=sum(case when name='啤酒' then [count]*price else 0 end),
白酒出货总金额=sum(case when name='白酒' then [count]*price else 0 end)
from
sale)T2
drop table buy
drop table sale
---------------------
--啤酒进货总金额 啤酒出货总金额 白酒进货总金额 白酒进货总金额
16000.00 13400.00 64000.00 65000.00
create table buy(id int, name nvarchar(10) , [count] int, price int)
insert buy select 1, '啤酒', 100, 50
insert buy select 2, '啤酒' , 200, 55
insert buy select 3, '白酒' , 100, 200
insert buy select 4, '白酒' , 200 , 220 create table sales(id int, name nvarchar(10) , [count] int, price int)
insert sales select 1, '啤酒', 80 , 55
insert sales select 2, '啤酒', 150 , 60
insert sales select 3, '白酒', 80 , 250
insert sales select 4, '白酒', 150 , 300 go
select
啤酒进货总金额 , 啤酒出货总金额=isnull(啤酒出货总金额,0),
白酒进货总金额 , 白酒出货总金额=isnull(白酒出货总金额,0)
from
(select
啤酒进货总金额=sum(case when name='啤酒' then [count]*price else 0 end),
白酒进货总金额=sum(case when name='白酒' then [count]*price else 0 end)
from
buy)T1,
(select
啤酒出货总金额=sum(case when name='啤酒' then [count]*price else 0 end),
白酒出货总金额=sum(case when name='白酒' then [count]*price else 0 end)
from
sales)T2
/*
啤酒进货总金额 啤酒出货总金额 白酒进货总金额 白酒出货总金额
----------- ----------- ----------- -----------
16000 13400 64000 65000(所影响的行数为 1 行)
*/
--drop table buy,sales
insert buy select 1, '啤酒', 100, 50
insert buy select 2, '啤酒' , 200, 55
insert buy select 3, '白酒' , 100, 200
insert buy select 4, '白酒' , 200 , 220 create table sales(id int, name nvarchar(10) , [count] int, price int)
insert sales select 1, '啤酒', 80 , 55
insert sales select 2, '啤酒', 150 , 60
insert sales select 3, '白酒', 80 , 250
insert sales select 4, '白酒', 150 , 300 go
declare @s1 nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000)
select
@s1=isnull(@s1+',','')+'['+Name+'进货总金额]=sum(case when Name='''+Name+''' then [count]*price else 0 end)',
@s2=isnull(@s2+',','')+'['+Name+'出货总金额]=sum(case when Name='''+Name+''' then [count]*price else 0 end)',
@s3=isnull(@s3+',','')+'['+Name+'进货总金额],['+Name+'出货总金额]'
from
buy t
group by
Name
order by (select min(ID) from buy where Name=t.Name)--排序一下
set @s1='select '+@s3+'from (select '+@s1+' from buy)t2,(select '+@s2+' from sales)t1'
--print @s1--显示语句
exec(@s1)
-- numbuyBeer numsaleBeer numbuyWine numsaleWine
declare @buy table (id int,name varchar(10),[count] int,price int)
insert into @buy select 1,'啤酒',100,50
insert into @buy select 2,'啤酒',200,55
insert into @buy select 3,'白酒',100,200
insert into @buy select 4,'白酒',200,220declare @sale table (id int,name varchar(10),[count] int,price int)
insert into @sale select 1,'啤酒',80,50
insert into @sale select 2,'啤酒',150,60
insert into @sale select 3,'白酒',80,250
insert into @sale select 4,'白酒',150,300select
sum(case when type=1 and name='啤酒' then [count]*price end) as '啤酒进货总金额',
sum(case when type=2 and name='啤酒' then [count]*price end) as '啤酒出货总金额',
sum(case when type=1 and name='白酒' then [count]*price end) as '白酒进货总金额',
sum(case when type=2 and name='白酒' then [count]*price end) as '白酒进货总金额'
from
(
select *,type=1 from @buy
union all
select *,type=2 from @sale
)tp16000 13000 64000 65000