首页 > 代码库 > sql: Compare Tables

sql: Compare Tables

2024-07-07 22:44:23   227人阅读

?
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

 


联系
我们