如何实现选择控制

Question

你最好把表结构用这样的形式贴出来，容易看一些:
 表sale(Bookid,bookpublisher,booksaleaccount,....）
 表Buy (字段1,字段2...) 阐明两张表的关系:
 然后提出自己的要求:这样明确很多，你上面的贴上网久了的同志们看了会眼花....还有，最好别用那么长的英文字段名，简写一下或者用个汉字代替，大家答题方便很多....（好像像我这样对E文不敏感的同胞有很多...呵呵！）

Answer 1

select a.bookid,a.bookpulisher,booksaleaccount-bookbuyaccount as bookstock
into #
from sale a inner join buy b
on a.bookid=b.bookid
and a.bookpublisher=b.bookpulisherupdate a
set bookstock=b.bookstock
from warehouse a inner join # b
on a.bookid=b.bookid
and a.bookpublisher=b.bookpulisherinsert into warehouse
select *
from # a
where not exists
(
select 1 from warehouse
where a.bookid=bookid
and a.bookpublisher=bookpulisher
)

Answer 2

sale(id,bookid,bookname,bookpublisher,booksaleaccount)
buy(id,bookid,bookname,bookpublisher,bookbuyaccount)
warehouse(id,bookid,bookname,bookpublisher,bookaccount)
前提条件：sale.bookid=buy.bookid and buy.bookpublisher=sale.bookpublisher 的情况下
若 bookid 已经存在于warehouse 则对buy 表中的bookbuyaccount求和减去 sale中的 booksaleaccount（求和）
如果不存在 则对bookid 进行插入 操作同

Answer 3

--Update
Update A Set bookaccount=IsNull(B.bookbuyaccount,0)-IsNull(C.booksaleaccount,0)
From warehouse A
Left Join 
(Select bookid,bookpublisher,SUM(bookbuyaccount) As bookbuyaccount From buy Group By bookid,bookpublisher) B
On A.bookid=B.bookid And A.bookpublisher=B.bookpublisher
Left Join
(Select bookid,bookpublisher,SUM(booksaleaccount) As booksaleaccount From sale Group By bookid,bookpublisher) C
On A.bookid=C.bookid And A.bookpublisher=C.bookpublisher
--Insert
Insert warehouse(bookid,bookname,bookpublisher,booksaleaccount)
Select A.bookid,A.bookname,A.bookpublisher,A.bookbuyaccount-B.booksaleaccount
From 
(Select bookid,bookpublisher,SUM(bookbuyaccount) As bookbuyaccount From buy Group By bookid,bookpublisher) A
Left Join
(Select bookid,bookpublisher,SUM(booksaleaccount) As booksaleaccount From sale Group By bookid,bookpublisher) B
On A.bookid=B.bookid And A.bookpublisher=B.bookpublisher
Where Not Exists(Select 1 From warehouse Where bookid=A.bookid And bookpublisher=A.bookpublisher)

Answer 4

修改下--Insert
Insert warehouse(bookid,bookname,bookpublisher,booksaleaccount)
Select A.bookid,A.bookname,A.bookpublisher,A.bookbuyaccount-IsNull(B.booksaleaccount,0)
From 
(Select bookid,bookname,bookpublisher,SUM(bookbuyaccount) As bookbuyaccount From buy Group By bookid,bookname,bookpublisher) A
Left Join
(Select bookid,bookpublisher,SUM(booksaleaccount) As booksaleaccount From sale Group By bookid,bookpublisher) B
On A.bookid=B.bookid And A.bookpublisher=B.bookpublisher
Where Not Exists(Select 1 From warehouse Where bookid=A.bookid And bookpublisher=A.bookpublisher)應該不存在，有sale 但沒有buy 的bookid，所以沒有考慮進這種情況。