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 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