sql: Compare Tables

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105

---使用 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 tables SELECT Label='Found IN BookInfoList, NOT IN InventoryBookList',* FROM (SELECT BookInfoID,BookInfoBarCode FROM BookInfoList  EXCEPT  SELECT  BookInventoryInfoID,BookInventoryBarCode FROM InventoryBookList where BookInventoryPlanId=1) x UNION ALL SELECT Label='Found IN InventoryBookList, NOT IN BookInfoList',* FROM (SELECT  BookInventoryInfoID,BookInventoryBarCode FROM InventoryBookList  where BookInventoryPlanId=1  EXCEPT  SELECT BookInfoID,BookInfoBarCode FROM BookInfoList) y GO   -- SQL Server T-SQL compare  tables for 2005 & 2008 SELECT Label='Found IN BookInfoList, NOT IN InventoryBookList',* FROM (SELECT BookInfoID,BookInfoBarCode FROM BookInfoList  EXCEPT  SELECT  BookInventoryInfoID,BookInventoryBarCode FROM InventoryBookList  where BookInventoryPlanId=2) x UNION ALL SELECT Label='Found IN InventoryBookList, NOT IN BookInfoList',* FROM (SELECT  BookInventoryInfoID,BookInventoryBarCode FROM InventoryBookList  where BookInventoryPlanId=2  EXCEPT  SELECT BookInfoID,BookInfoBarCode FROM BookInfoList) y GO   -- -- SQL find rows present in both tables SELECT BookInfoID,BookInfoBarCode FROM BookInfoList INTERSECT SELECT 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 results SELECT BookInfoID,BookInfoBarCode FROM BookInfoList   LEFT OUTER JOIN InventoryBookList     ON InventoryBookList.BookInventoryInfoID=BookInfoList.BookInfoID WHERE InventoryBookList.BookInventoryPlanId=1 GO   select * FROM InventoryBookList WHERE InventoryBookList.BookInventoryPlanId=1   -- select * FROM BookInfoList left join InventoryBookList on InventoryBookList.BookInventoryInfoID=BookInfoList.BookInfoID WHERE InventoryBookList.BookInventoryPlanId=1     --存在相同的 select * FROM InventoryBookList left join BookInfoList on InventoryBookList.BookInventoryInfoID=BookInfoList.BookInfoID WHERE InventoryBookList.BookInventoryPlanId=1     ---圖書註銷，報廢 IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_BookCancellationSearch') DROP PROCEDURE proc_Select_BookCancellationSearch GO CREATE PROCEDURE proc_Select_BookCancellationSearch (  @BookInfoCancellStar Datetime,  @BookInfoCancellEnd Datetime,  @search nvarchar(100) ) as declare @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 +'%'')' end set @sql=@sql+@where+' order by BookCancelInfoDate desc' print @sql exec(@sql) GO

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

---在借和注销的书籍 IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS       WHERE TABLE_NAME = 'View_BookLendingCancellation') DROP VIEW View_BookLendingCancellation GO CREATE VIEW View_BookLendingCancellation AS select BookLendingInfoID,BookLendingInfoBarCode from BookLendingList where BookLendingReturn is null  --在借的書 union select BookCancelInfoID,BookCancelBarCode from BookCancellationList                                   --註銷的書 GO   select * 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  --在借的書 union select BookInventoryInfoID,BookInventoryBarCode from InventoryBookList  where BookInventoryPlanId=1   --盤點的書 union select BookCancelInfoID,BookCancelBarCode from BookCancellationList                                   --註銷的書 GO       --计算盘点问题 declare @BookInventoryPlanId int set @BookInventoryPlanId=1 drop table #a select BookLendingInfoID,BookLendingInfoBarCode  into #a  from BookLendingList where BookLendingReturn is null insert into #a(BookLendingInfoID,BookLendingInfoBarCode) select BookInventoryInfoID,BookInventoryBarCode from InventoryBookList  where BookInventoryPlanId=@BookInventoryPlanId insert 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