34,590
社区成员
发帖
与我相关
我的任务
分享
declare @tb1 table (stknum varchar(10),stkname varchar(10))
insert into @tb1 select '001','钢笔'
union all select '002','铅笔'
union all select '003','圆珠笔'
union all select '004','毛笔'
declare @tb2 table (shtnum varchar(10),dtimecreate datetime,buynum int,sellnum int)
insert into @tb2 select '001','2009-1-1',20,15
UNION ALL SELECT '002','2009-1-1',10,12
UNION ALL SELECT '002','2009-1-2',14,2
UNION ALL SELECT '003','2009-1-3',15,10
UNION ALL SELECT '001','2009-1-4',15,10
UNION ALL SELECT '003','2009-1-4',10,10
UNION ALL SELECT '001','2009-1-5',10,12
select a.stkname,isnull(b.buynum,0) buynum,isnull(b.sellnum,0) sellnum from @tb1 a left join
(select shtnum,SUM(buynum) buynum,SUM(sellnum) sellnum from @tb2 group by shtnum) b
on a.stknum=b.shtnum
stkname buynum sellnum
---------- ----------- -----------
钢笔 45 37
铅笔 24 14
圆珠笔 25 20
毛笔 0 0
(4 行受影响)
create table #1 (stkNum varchar(10),stkName varchar(20))
insert into #1 select '001','钢笔 '
insert into #1 select '002','铅笔 '
insert into #1 select '003','圆珠笔'
insert into #1 select '004','毛笔 '
create table #2 (shtNum varchar(10),dtimeCreate datetime,buysellMode varchar(10),Num int)
insert into #2 select '001','2009-1-1','s',15
insert into #2 select '002','2009-1-1','s',12
insert into #2 select '002','2009-1-2','b',2
insert into #2 select '003','2009-1-3','s',10
insert into #2 select '001','2009-1-4','b',10
insert into #2 select '003','2009-1-4','s',10
insert into #2 select '001','2009-1-5','b',12
select a.stkName
,buyNum = sum(case when buysellMode = 'b' then num else 0 end)
,sellNum = sum(case when buysellMode = 's' then num else 0 end)
from #1 a
left join #2 b on a.stkNum = b.shtNum
group by a.stkName
/*
stkName buyNum sellNum
-------------------- ----------- -----------
钢笔 22 15
毛笔 0 0
铅笔 2 12
圆珠笔 0 20
(4 row(s) affected)
*/
declare @t1 table(stkNum varchar(10),stkName varchar(20))
insert into @t1 select '001','钢笔 '
insert into @t1 select '002','铅笔 '
insert into @t1 select '003','圆珠笔'
insert into @t1 select '004','毛笔 '
declare @t2 table(shtNum varchar(10),dtimeCreate datetime,buyNum int,sellNum int)
insert into @t2 select '001','2009-1-1',20,15
insert into @t2 select '002','2009-1-1',10,12
insert into @t2 select '002','2009-1-2',14,2
insert into @t2 select '003','2009-1-3',15,10
insert into @t2 select '001','2009-1-4',15,10
insert into @t2 select '003','2009-1-4',10,10
insert into @t2 select '001','2009-1-5',10,12
select
a.stkName,isnull(sum(b.buyNum),0) as buyNum,isnull(sum(b.sellNum),0) as sellNum
from
@t1 a
left join
@t2 b
on
a.stkNum=b.shtNum
group by
a.stkNum,a.stkName
order by
a.stkNum
/*
stkName buyNum sellNum
-------------------- ----------- -----------
钢笔 45 37
铅笔 24 14
圆珠笔 25 20
毛笔 0 0
*/
-----存储过程
create procedure pro_getDateil
as
begin
select a.stkName,sum(isnull(buyNum,0)) as buyNum,sum(isnull(sellNum,0)) as sellNum
from stk_productInfo a
left join stk_businessInfo b on a.stkNum = b.stkNum
group by a.stkName
end
--------创建视图
create view vw_getDateil
as
select a.stkName,sum(isnull(buyNum,0)) as buyNum,sum(isnull(sellNum,0)) as sellNum
from stk_productInfo a
left join stk_businessInfo b on a.stkNum = b.stkNum
group by a.stkName
--更正
SELECT A.stkName,ISNULL(B.buyNum,0),ISNULL(B.sellNum,0) FROM stk_productInfo A
LEFT JOIN
(SELECT stkName,SUM(buyNum)buyNum,SUM(sellNum)sellNum FROM stk_businessInfo GROUP BY stkName)B
ON A.stkNum=B.stkNum
select
a.stkName,isnull(sum(b.buyNum),0) as buyNum,isnull(sum(b.sellNum),0) as sellNum
from
stk_productInfo a
left join
stk_businessInfo b
on
a.shtNum=b.shtNum
group by
a.stkName
--更正
SELECT A.stkName,B.buyNum,B.sellNum FROM stk_productInfo A
LEFT JOIN
(SELECT stkName,SUM(buyNum)buyNum,SUM(sellNum)sellNum FROM stk_businessInfo GROUP BY stkName)B
ON A.stkNum=B.stkNum
SELECT A.stkName,B.buyNum,B.sellNum FROM stk_productInfo A
INNER JOIN
(SELECT stkName,SUM(buyNum)buyNum,SUM(sellNum)sellNum FROM stk_businessInfo GROUP BY stkName)B
ON A.stkName=B.stkName