sql: Compare Tables

浏览数：23 / 时间：2015年06月12日

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

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