sql: Compare Tables

---使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式select * from BookInfoList--存在不同的select BookInfoID,BookInfoBarCode from BookInfoList where BookInfoStatus=1 except select BookInventoryInfoID,BookInventoryBarCode from InventoryBookList where BookInventoryPlanId=1 --存在相同的select BookInfoID,BookInfoBarCode from BookInfoList where exists (select BookInventoryInfoID,BookInventoryBarCode from InventoryBookList  where InventoryBookList.BookInventoryInfoID=BookInfoList.BookInfoID and  BookInventoryPlanId=1)---存在不同的select BookInfoID,BookInfoBarCode from BookInfoList where not exists (select BookInventoryInfoID,BookInventoryBarCode from InventoryBookList  where InventoryBookList.BookInventoryInfoID=BookInfoList.BookInfoID and  BookInventoryPlanId=1)select BookInventoryInfoID,BookInventoryBarCode from InventoryBookList  where  exists (select BookInfoID,BookInfoBarCode from BookInfoList where InventoryBookList.BookInventoryInfoID=BookInfoList.BookInfoID)--書盤點到的書藉select BookInventoryInfoID,BookInventoryBarCode from InventoryBookList  where BookInventoryPlanId=1--查找没盘点没有盘点到的书藉,还要考虑在借的书藉select * from View_BookInfoList where BookInfoID not in (select BookInventoryInfoID from InventoryBookList  where BookInventoryPlanId=1)select * from View_BookInfoList where BookInfoID not in (select BookInventoryInfoID from InventoryBookList  where BookInventoryPlanId=2)-- TEMPLATE - SQL Server T-SQL compare two tablesSELECT Label='Found IN BookInfoList, NOT IN InventoryBookList',* FROM (SELECT BookInfoID,BookInfoBarCode FROM BookInfoList EXCEPT SELECT  BookInventoryInfoID,BookInventoryBarCode FROM InventoryBookList where BookInventoryPlanId=1) xUNION ALLSELECT Label='Found IN InventoryBookList, NOT IN BookInfoList',* FROM(SELECT  BookInventoryInfoID,BookInventoryBarCode FROM InventoryBookList  where BookInventoryPlanId=1 EXCEPT SELECT BookInfoID,BookInfoBarCode FROM BookInfoList) yGO-- SQL Server T-SQL compare  tables for 2005 & 2008SELECT Label='Found IN BookInfoList, NOT IN InventoryBookList',* FROM (SELECT BookInfoID,BookInfoBarCode FROM BookInfoList EXCEPT SELECT  BookInventoryInfoID,BookInventoryBarCode FROM InventoryBookList  where BookInventoryPlanId=2) xUNION ALLSELECT Label='Found IN InventoryBookList, NOT IN BookInfoList',* FROM(SELECT  BookInventoryInfoID,BookInventoryBarCode FROM InventoryBookList  where BookInventoryPlanId=2 EXCEPT SELECT BookInfoID,BookInfoBarCode FROM BookInfoList) yGO---- SQL find rows PResent in both tablesSELECT BookInfoID,BookInfoBarCode FROM BookInfoListINTERSECTSELECT BookInventoryInfoID,BookInventoryBarCode FROM InventoryBookList  where BookInventoryPlanId=1---SELECT BookInfoID,BookInfoBarCode FROM BookInfoList WHERE NOT EXISTS (SELECT BookInventoryInfoID,BookInventoryBarCode FROM InventoryBookList                     WHERE InventoryBookList.BookInventoryInfoID=BookInfoList.BookInfoID and  InventoryBookList.BookInventoryPlanId=1) ---- Alternate  query - same resultsSELECT BookInfoID,BookInfoBarCode FROM BookInfoList   LEFT OUTER JOIN InventoryBookList     ON InventoryBookList.BookInventoryInfoID=BookInfoList.BookInfoIDWHERE InventoryBookList.BookInventoryPlanId=1GOselect * FROM InventoryBookList WHERE InventoryBookList.BookInventoryPlanId=1--select * FROM BookInfoList left join InventoryBookList on InventoryBookList.BookInventoryInfoID=BookInfoList.BookInfoIDWHERE InventoryBookList.BookInventoryPlanId=1--存在相同的select * FROM InventoryBookList left join BookInfoList on InventoryBookList.BookInventoryInfoID=BookInfoList.BookInfoIDWHERE InventoryBookList.BookInventoryPlanId=1---圖書註銷，報廢IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_BookCancellationSearch')DROP PROCEDURE proc_Select_BookCancellationSearchGOCREATE PROCEDURE proc_Select_BookCancellationSearch( @BookInfoCancellStar Datetime, @BookInfoCancellEnd Datetime, @search nvarchar(100))asdeclare @sql nvarchar(4000),@where nvarchar(4000)set @sql='select * from View_BookCancellationList where  BookCancelInfoDate>='''+ cast(@BookInfoCancellStar as varchar)+''' and BookCancelInfoDate<='''+cast(@BookInfoCancellEnd as varchar)+''''set @where=''if @Search<>''begin set @where=@where+' and (BookInfoISBN like ''%'+@search +'%'' or BookInfoBarCode like ''%'+@search +'%'' or BookCancelInfoDescription like ''%'+@search +'%'' or BookInfoName like ''%'+@search +'%''  or BookInfoRemarks  like ''%'+@search +'%'' or BookKindName like ''%'+@search +'%''  or AuthorName like ''%'+@search +'%'')'endset @sql=@sql+@where+' order by BookCancelInfoDate desc'print @sqlexec(@sql)GO 

---在借和注销的书籍IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS      WHERE TABLE_NAME = 'View_BookLendingCancellation')DROP VIEW View_BookLendingCancellationGOCREATE VIEW View_BookLendingCancellationASselect BookLendingInfoID,BookLendingInfoBarCode from BookLendingList where BookLendingReturn is null  --在借的書unionselect BookCancelInfoID,BookCancelBarCode from BookCancellationList  --註銷的書GOselect * from View_BookLendingCancellation--计算在馆的书select * from View_BookInfoList where not exists (select BookLendingInfoID,BookLendingInfoBarCode  from View_BookLendingCancellation  where View_BookLendingCancellation.BookLendingInfoID=View_BookInfoList.BookInfoID)---在借和注销，盘点的书籍select BookLendingInfoID,BookLendingInfoBarCode from BookLendingList where BookLendingReturn is null  --在借的書unionselect BookInventoryInfoID,BookInventoryBarCode from InventoryBookList  where BookInventoryPlanId=1   --盤點的書unionselect BookCancelInfoID,BookCancelBarCode from BookCancellationList  --註銷的書GO--计算盘点问题declare @BookInventoryPlanId int set @BookInventoryPlanId=1drop table #aselect BookLendingInfoID,BookLendingInfoBarCode  into #a  from BookLendingList where BookLendingReturn is null insert into #a(BookLendingInfoID,BookLendingInfoBarCode) select BookInventoryInfoID,BookInventoryBarCode from InventoryBookList  where BookInventoryPlanId=@BookInventoryPlanIdinsert into #a(BookLendingInfoID,BookLendingInfoBarCode) select BookCancelInfoID,BookCancelBarCode from BookCancellationList--select * from #a select * from View_BookInfoList where not exists (select * from #a where #a.BookLendingInfoID=View_BookInfoList.BookInfoID)select BookLendingInfoID,BookLendingInfoBarCode from BookLendingList where BookLendingReturn is null union select BookInventoryInfoID,BookInventoryBarCode from InventoryBookList  where BookInventoryPlanId=@BookInventoryPlanId  union select BookCancelInfoID,BookCancelBarCode from BookCancellationList