关于左连接取值的问题？谢谢
drop table input
drop table inputSub
drop table output
drop table outputSub
create table input
(
id int, --编号
name nvarchar(20), --名称
inNum int --接收数
)
create table inputSub
(
id int,
printed char(1)
)
create table output
(
id int,
name nvarchar(20),
outNum int --发送数
)
create table outputSub
(
id int,
printed char(1)
)
---表说明---------------
--input ：接收记录
--inputSub ：接收确认,形成实际接收
--output ：发送记录
--outpubSub ：发送确认,形成实际发送
---测试数据----------
insert into input (id,name,inNum) values (1,'AA',20)
insert into input (id,name,inNum) values (2,'BB',10)
insert into input (id,name,inNum) values (3,'CC',30)
insert into input (id,name,inNum) values (4,'AA',40)
insert into input (id,name,inNum) values (5,'DD',15)
insert into input (id,name,inNum) values (6,'EE',20)
insert into input (id,name,inNum) values (7,'BB',50)
insert into input (id,name,inNum) values (8,'AA',20)
insert into input (id,name,inNum) values (9,'FF',35)
insert into inputSub (id,printed) values (1,'Y')
insert into inputSub (id,printed) values (3,'Y')
insert into inputSub (id,printed) values (5,'Y')
insert into inputSub (id,printed) values (7,'Y')
insert into inputSub (id,printed) values (9,'Y')
insert into output (id,name, outnum) values (1,'BB',5)
insert into output (id,name, outnum) values (2,'BB',10)
insert into output (id,name, outnum) values (3,'CC',10)
insert into output (id,name, outnum) values (4,'CC',25)
insert into output (id,name, outnum) values (5,'EE',10)
insert into output (id,name, outnum) values (6,'DD',5)
insert into outputSub (id,printed) values (2,'Y')
insert into outputSub (id,printed) values (4,'Y')
insert into outputSub (id,printed) values (6,'Y')
-------------以上为脚本-------------------------------------
--查询要求：
--同一名称的实际接收数,即: 实际接收的 - 实际发送的
-- 第一结果集
select input.name, sum(inNum)
from input
inner join inputSub on input.id= inputSub.id and inputSub.printed = 'Y'
group by input.name
AA20
BB50
CC30
DD15
FF35
--第二结果集
select output.name,sum(outNum)
from output
inner join outputSub on output.id= outputSub.id and outputSub.printed = 'Y'
group by output.name
BB10
CC25
DD5
要求第一结果集去 左连接 第二结果集 条件是 input.name = output.name
AA20
BB40
CC5
DD10
FF35
我的语句：得不到结果呀
select input.name, sum(inNum) - sum(outNum)
from input
inner join inputSub on input.id= inputSub.id and inputSub.printed = 'Y'
left join output on input.name = output.name
left join outputSub on output.id = outputSub.id and outputSub.printed = 'Y'
group by input.name
[解决办法]
- SQL code
create table input ( id int, --编号 name nvarchar(20), --名称 inNum int --接收数 ) create table inputSub ( id int, printed char(1) ) create table output ( id int, name nvarchar(20), outNum int --发送数 ) create table outputSub ( id int, printed char(1) ) insert into input (id,name,inNum) values (1, 'AA ',20) insert into input (id,name,inNum) values (2, 'BB ',10) insert into input (id,name,inNum) values (3, 'CC ',30) insert into input (id,name,inNum) values (4, 'AA ',40) insert into input (id,name,inNum) values (5, 'DD ',15) insert into input (id,name,inNum) values (6, 'EE ',20) insert into input (id,name,inNum) values (7, 'BB ',50) insert into input (id,name,inNum) values (8, 'AA ',20) insert into input (id,name,inNum) values (9, 'FF ',35) insert into inputSub (id,printed) values (1, 'Y ') insert into inputSub (id,printed) values (3, 'Y ') insert into inputSub (id,printed) values (5, 'Y ') insert into inputSub (id,printed) values (7, 'Y ') insert into inputSub (id,printed) values (9, 'Y ') insert into output (id,name, outnum) values (1, 'BB ',5) insert into output (id,name, outnum) values (2, 'BB ',10) insert into output (id,name, outnum) values (3, 'CC ',10) insert into output (id,name, outnum) values (4, 'CC ',25) insert into output (id,name, outnum) values (5, 'EE ',10) insert into output (id,name, outnum) values (6, 'DD ',5) insert into outputSub (id,printed) values (2, 'Y ') insert into outputSub (id,printed) values (4, 'Y ') insert into outputSub (id,printed) values (6, 'Y ') select a.name,sum(innum) as num from (select name, innum from input a,inputsub b where a.id=b.idunion allselect name, -outnum from [output] a,outputsub b where a.id=b.id) a group by name/* name num -------------------- ----------- AA 20BB 40CC 5DD 10FF 35（所影响的行数为 5 行） */
[解决办法]
- SQL code
select
t1.name,
[库存]=inNum-isnull(OutNum,0)
from
(select input.name, sum(inNum) as inNum
from input
inner join inputSub on input.id= inputSub.id and inputSub.printed = 'Y '
group by input.name )t1
left join
(select output.name,sum(outNum) as OutNum
from [output]
inner join outputSub on output.id= outputSub.id and outputSub.printed = 'Y '
group by output.name )t2 on t1.name=t2.name