select isnull(m.pid,n.pid) pid , isnull(m.quantity ,0) quantity , isnull(n.squantity , 0) squantity , isnull(m.quantity ,0) - isnull(n.squantity , 0) from
(
select pid ,sum(quantity) quantity from Product group by pid
) m
full join
(
select pid ,sum(squantity) squantity from stock group by pid
) n
on m.pid = n.pid
(
select pid ,sum(quantity) quantity from Product group by pid
) m
full join
(
select pid ,sum(squantity) squantity from stock group by pid
) n
on m.pid = n.pid
是不是表示004有库存却在基础表中没有?
IsNull(A.pid, B.pid) As pid,
IsNull(A.quantity, 0) As quantity,
IsNull(B.squantity, 0) As squantity,
IsNull(A.quantity, 0) - IsNull(B.squantity, 0) As [quantity - squantity]
From
(Select pid, SUM(quantity) As quantity From Product Group By pid) A
Full Join
(Select pid, SUM(squantity) As squantity From stock Group By pid) B
On A.pid = B.pid
drop table Product
gocreate table Product(pid varchar(10),quantity int)
insert into Product(pid,quantity) values('001', 1)
insert into Product(pid,quantity) values('002', 2)
insert into Product(pid,quantity) values('001', 2)
insert into Product(pid,quantity) values('003', 5)
goif object_id('pubs..stock') is not null
drop table stock
gocreate table stock(sid int,pid varchar(10),squantity int)
insert into stock(sid,pid,squantity) values(1, '001', 3)
insert into stock(sid,pid,squantity) values(2, '004', 2)
insert into stock(sid,pid,squantity) values(3, '002', 2)
goselect isnull(m.pid,n.pid) pid , isnull(m.quantity ,0) quantity , isnull(n.squantity , 0) squantity , isnull(m.quantity ,0) - isnull(n.squantity , 0) from
(
select pid ,sum(quantity) quantity from Product group by pid
) m
full join
(
select pid ,sum(squantity) squantity from stock group by pid
) n
on m.pid = n.pid
drop table stock
drop table Product/*
pid quantity squantity
---------- ----------- ----------- -----------
001 3 3 0
002 2 2 0
003 5 0 5
004 0 2 -2(所影响的行数为 4 行)
*/
drop table Product
gocreate table Product(pid varchar(10),quantity int)
insert into Product(pid,quantity) values('001', 1)
insert into Product(pid,quantity) values('002', 2)
insert into Product(pid,quantity) values('001', 2)
insert into Product(pid,quantity) values('003', 5)
goif object_id('pubs..stock') is not null
drop table stock
gocreate table stock(sid int,pid varchar(10),squantity int)
insert into stock(sid,pid,squantity) values(1, '001', 3)
insert into stock(sid,pid,squantity) values(2, '004', 2)
insert into stock(sid,pid,squantity) values(3, '002', 2)
goselect isnull(m.pid,n.pid) pid , isnull(m.quantity ,0) quantity , isnull(n.squantity , 0) squantity , isnull(m.quantity ,0) - isnull(n.squantity , 0) quantity_squantity from
(
select pid ,sum(quantity) quantity from Product group by pid
) m
full join
(
select pid ,sum(squantity) squantity from stock group by pid
) n
on m.pid = n.pid
drop table stock
drop table Product/*
pid quantity squantity quantity_squantity
---------- ----------- ----------- ------------------
001 3 3 0
002 2 2 0
003 5 0 5
004 0 2 -2(所影响的行数为 4 行)
*/
Insert Into Product Select '001', 1
Union All Select '002', 2
Union All Select '001', 2
Union All Select '003', 5
GOCreate Table stock(sid Int, pid Varchar(10), squantity Int)
Insert Into stock Select 1, '001', 3
Union All Select 2, '004', 2
Union All Select 3, '002', 2
GO
Select
IsNull(A.pid, B.pid) As pid,
IsNull(A.quantity, 0) As quantity,
IsNull(B.squantity, 0) As squantity,
IsNull(A.quantity, 0) - IsNull(B.squantity, 0) As [quantity - squantity]
From
(Select pid, SUM(quantity) As quantity From Product Group By pid) A
Full Join
(Select pid, SUM(squantity) As squantity From stock Group By pid) B
On A.pid = B.pid
GO
Drop Table Product, stock
--Result
/*
pid quantity squantity quantity - squantity
001 3 3 0
002 2 2 0
003 5 0 5
004 0 2 -2
*/
union all
select pid , '' as tt, sum(squantity) as yy from stock group by pid)agroup by a.pid
a.pid=b.pid