---使用 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
---在借和注销的书籍
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