数据汇总问题
我有表product和表stock
表Product:
pid quantity
001 1
002 2
001 2
003 5
表stock:
sid pid squantity
1 001 3
2 004 2
3 002 2
想得到如下结果:
pid sum(quantity) sum(squantity) sum(quantity)-sum(squantity)
001 3 3 0
002 2 2 0
003 5 0 5
004 0 2 -2
谢谢指点.
[解决办法]
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
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
[解决办法]
if object_id( 'pubs..Product ') is not null
drop table Product
go
create 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)
go
if object_id( 'pubs..stock ') is not null
drop table stock
go
create 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)
go
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
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 行）
*/
[解决办法]
if object_id( 'pubs..Product ') is not null
drop table Product
go
create 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)
go
if object_id( 'pubs..stock ') is not null
drop table stock
go
create 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)
go
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) 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 行）
*/
[解决办法]
Create Table Product(pid Varchar(10), quantity Int)
Insert Into Product Select '001 ', 1
Union All Select '002 ', 2
Union All Select '001 ', 2
Union All Select '003 ', 5
GO
Create 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
/*
pidquantitysquantityquantity - squantity
001330
002220
003505
00402-2
*/
[解决办法]
select a.pid,sum(a.tt),sum(a.yy),sum(a.tt)-sum(a.yy) from
(select pid ,sum(quantity) as tt, ' ' as yy from product group by pid
union all
select pid , ' ' as tt, sum(squantity) as yy from stock group by pid)a
group by a.pid
[解决办法]
select isnull(a.pid ,b.pid) pid , isnull(a.quantity,0) quantity,isnull(b.squantity,0) squantity from (select pid ,sum(quantity) quantity from product group by pid) a full join stock b on
a.pid=b.pid
