求一个Sql查询语句!!急！！高分相送

Question

1.table 
inStore 
 
id,prodno,prodname,spec,amount,price
------------------------------------
1  01     Pen      Blue   10    10.5
2  01     Pen      Blue   20    10.5
3  01     Pen      Blue   12    10.5
4  01     Pen      Black  10    10.5
5  01     Pen      Red   10    10.52.table
outstore 
id,prodno,prodname,spec,amount,price
------------------------------------
1  01     Pen      Blue   3     10.5
2  01     Pen      Blue   4     10.5
3  01     Pen      Black  8     10.5Instore里的数量之和减去OutStore里的数量之和然后获得下面数据，如果OutStore里面没有的直接显示数量和
1  01     Pen      Blue   35    10.5
1  01     Pen      Black  2     10.5
1  01     Pen      Red    10    10.5请问如何写这个SQL，

Answer 1

like this 
select id=count(1),
amount=(select sum(amount)from instore c where c.prodno=a.prodno and spec=a.spec),
amountout=(select sum(amount)from outstore  where prodno=a.prodno and spec=a.spec),
a.spec,amount=(select sum(amount)from instore c where c.prodno=a.prodno and spec=a.spec)-(select sum(amount)from outstore  where prodno=a.prodno and spec=a.spec)
from instore a,outstore b 
where a.prodno=b.prodno and a.spec=b.spec
group by a.spec,a.prodno但是要显示OutStore表里面没有的比如red

Answer 2

Select a.prodno,a.prodname,a.spec,a.amount-b.amount as amount,a.price
from (
Select prodno,prodname,spec,amount=sum(amount),price
from InStore group by prodno,prodname,spec,price
) as a Left Join (Select prodno,prodname,spec,amount=sum(amount),price
  from outstore group by prodno,prodname,spec,price) as b
 on a.prodno=b.prodno and a.spec=b.spec

Answer 3

Select a.prodno,a.prodname,a.spec,a.amount-IsNULL(b.amount,0) as amount,a.price
from ( Select prodno,prodname,spec,amount=sum(amount),price
       from InStore group by prodno,prodname,spec,price ) as a 
        Left Join (Select prodno,prodname,spec,amount=sum(amount),price
                   from outstore group by prodno,prodname,spec,price) as b
 on a.prodno=b.prodno and a.spec=b.spec

Answer 4

Select 
A.prodno,
A.prodname,
A.spec,
A.amount - IsNull(B.amount, 0) As amount,
A.price
From
(Select prodno,prodname,spec,SUM(amount) As amount,price From inStore Group By prodno,prodname,spec,price) A
Left Join
(Select prodno,prodname,spec,SUM(amount) As amount,price From outstore Group By prodno,prodname,spec,price) B
On A.prodno =B.prodno And A.prodname = B.prodname And A.spec = B.spec And A.price = B.price

Answer 5

或者Select
prodno,
prodname,
spec,
inamount - outamount As amount,
price
From
(Select prodno,prodname,spec,amount As inamount, 0 As outamount, price From inStore
Union All
Select prodno,prodname,spec,1 As inamount, amount As outamount, price From outstore) A
Group By 
prodno,
prodname,
spec,
price

Answer 6

有bug
Select
prodno,
prodname,
spec,
SUM(inamount - outamount) As amount,
price
From
(Select prodno,prodname,spec,amount As inamount, 0 As outamount, price From inStore
Union All
Select prodno,prodname,spec,0 As inamount, amount As outamount, price From outstore) A
Group By 
prodno,
prodname,
spec,
price

Answer 7

--建立測試環境
Create Table inStore 
(id Int,
 prodno Char(2),
 prodname Varchar(10),
 spec Varchar(10),
 amount Int,
 price Numeric(10, 1))Insert inStore Select 1,  '01',     'Pen',      'Blue',    10,    10.5
Union All Select 2,  '01',     'Pen',      'Blue',    20,    10.5
Union All Select 3,  '01',     'Pen',      'Blue',    12,    10.5
Union All Select 4,  '01',     'Pen',      'Black',  10,    10.5
Union All Select 5,  '01',     'Pen',      'Red',     10,    10.5Create Table outstore 
(id Int,
 prodno Char(2),
 prodname Varchar(10),
 spec Varchar(10),
 amount Int,
 price Numeric(10, 1))Insert outstore Select 1,  '01',     'Pen',      'Blue',   3,     10.5
Union All Select 2,  '01',     'Pen',      'Blue',    4,     10.5
Union All Select 3,  '01',     'Pen',      'Black',  8,     10.5
GO
--測試
--方法一：
Select 
A.prodno,
A.prodname,
A.spec,
A.amount - IsNull(B.amount, 0) As amount,
A.price
From
(Select prodno,prodname,spec,SUM(amount) As amount,price From inStore Group By prodno,prodname,spec,price) A
Left Join
(Select prodno,prodname,spec,SUM(amount) As amount,price From outstore Group By prodno,prodname,spec,price) B
On A.prodno =B.prodno And A.prodname = B.prodname And A.spec = B.spec And A.price = B.price--方法二：
Select
prodno,
prodname,
spec,
SUM(inamount - outamount) As amount,
price
From
(Select prodno,prodname,spec,amount As inamount, 0 As outamount, price From inStore
Union All
Select prodno,prodname,spec,0 As inamount, amount As outamount, price From outstore) A
Group By 
prodno,
prodname,
spec,
price
GO
--刪除測試環境
Drop Table inStore, outstore
--結果
/*
prodno prodname spec amount price
01 Pen Black 2 10.5
01 Pen Blue 35 10.5
01 Pen Red 10 10.5
*/