SQL 比较两个表中的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4602083/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL compare data from two tables
提问by nikky
I have 2 tables TableAand TableBwhich have the same format of column for example both tables TableAand TableBhave columns
我有2个表TableA和TableB其具有例如列的相同的格式两个表TableA和TableB具有列
A B C D E F
where A and B are the primary keys.
其中 A 和 B 是主键。
How to write SQL to check that if TableAand TableBthat have the same primary keys contains exactly the same value in every columns.
如何编写SQL来检查，如果TableA与TableB具有相同主键正好包含在每列中的值相同。
It means that these two tables has exactly the same data.
这意味着这两个表具有完全相同的数据。
回答by dietbuddha
You should be able to "MINUS" or "EXCEPT" depending on the flavor of SQL used by your DBMS.
根据您的 DBMS 使用的 SQL 风格，您应该能够“减去”或“除外”。
select * from tableA
minus
select * from tableB
If the query returns no rows then the data is exactly the same.
如果查询未返回任何行，则数据完全相同。
回答by onedaywhen
Using relational operators:
使用关系运算符：
SELECT * FROM TableA
UNION
SELECT * FROM TableB
EXCEPT
SELECT * FROM TableA
INTERSECT
SELECT * FROM TableB;
Change EXCEPTto MINUSfor Oracle.
更改EXCEPT到MINUS甲骨文。
Slightly picky point: the above relies on operator precedence, which according to the SQL Standard is implementation dependent, so YMMV. It works for SQL Server, for which the precedence is:
稍微挑剔一点：以上依赖于运算符优先级，根据 SQL 标准，这是依赖于实现的，所以 YMMV。它适用于 SQL Server，其优先级为：
- Expressions in parentheses
INTERSECTEXCEPTandUNIONevaluated from left to right.
- 括号中的表达式
INTERSECTEXCEPT并UNION从左到右评估。
回答by jzd
dietbuddha has a nice answer. In cases where you don't have a MINUS or EXCEPT, one option is to do a union all between the tables, group by with all the columns and make sure there is two of everything:
Dietbuddha 有一个很好的答案。在没有 MINUS 或 EXCEPT 的情况下，一种选择是在表之间进行联合，将所有列分组，并确保有两个：
SELECT col1, col2, col3
FROM
(SELECT * FROM tableA
UNION ALL
SELECT * FROM tableB) data
GROUP BY col1, col2, col3
HAVING count(*)!=2
回答by imiz
SELECT c.ID
FROM clients c
WHERE EXISTS(SELECT c2.ID
FROM clients2 c2
WHERE c2.ID = c.ID);
Will return all ID's that are the SAME in both tables. To get the differences change EXISTS to NOT EXISTS.
将返回两个表中相同的所有 ID。要获得差异，请将 EXISTS 更改为 NOT EXISTS。
回答by Robert Sievers
Taking the script from onedaywhen, I modified it to also show which table each entry comes from.
从 onedaywhen 中获取脚本，我对其进行了修改，以显示每个条目来自哪个表。
DECLARE @table1 NVARCHAR(80)= 'table 1 name'
DECLARE @table2 NVARCHAR(80)= 'table 2 name'
DECLARE @sql NVARCHAR (1000)
SET @sql =
'
SELECT ''' + @table1 + ''' AS table_name,* FROM
(
SELECT * FROM ' + @table1 + '
EXCEPT
SELECT * FROM ' + @table2 + '
) x
UNION
SELECT ''' + @table2 + ''' AS table_name,* FROM
(
SELECT * FROM ' + @table2 + '
EXCEPT
SELECT * FROM ' + @table1 + '
) y
'
EXEC sp_executesql @stmt = @sql
回答by Arnaud ALLAVENA
just to complet, a proc stored using except method to compare 2 tables and give result in same table with 3 errors status, ADD, DEL, GAP table must have same PK, you declare the 2 tables and fields to compare of 1 or both table
只是为了完成，使用 except 方法存储的 proc 比较 2 个表并在同一个表中给出结果 3 个错误状态，ADD、DEL、GAP 表必须具有相同的 PK，您声明 2 个表和字段来比较 1 个或两个表
Just use like this ps_TableGap 'tbl1','Tbl2','fld1,fld2,fld3','fld4'fld5'fld6' (optional)
就像这样使用 ps_TableGap 'tbl1','Tbl2','fld1,fld2,fld3','fld4'fld5'fld6'（可选）
/****** Object: StoredProcedure [dbo].[ps_TableGap] Script Date: 10/03/2013 16:03:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Arnaud ALLAVENA
-- Create date: 03.10.2013
-- Description: Compare tables
-- =============================================
create PROCEDURE [dbo].[ps_TableGap]
-- Add the parameters for the stored procedure here
@Tbl1 as varchar(100),@Tbl2 as varchar(100),@Fld1 as varchar(1000), @Fld2 as varchar(1000)= ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Variables
--@Tbl1 = table 1
--@Tbl2 = table 2
--@Fld1 = Fields to compare from table 1
--@Fld2 Fields to compare from table 2
Declare @SQL varchar(8000)= '' --SQL statements
Declare @nLoop int = 1 --loop counter
Declare @Pk varchar(1000)= '' --primary key(s)
Declare @Pk1 varchar(1000)= '' --first field of primary key
declare @strTmp varchar(50) = '' --returns value in Pk determination
declare @FldTmp varchar (1000) = '' --temporarily fields for alias calculation
--If @Fld2 empty we take @Fld1
--fields rules: fields to be compare must be in same order and type - always returns Gap
If @Fld2 = '' Set @Fld2 = @Fld1
--Change @Fld2 with Alias prefix xxx become _xxx
while charindex(',',@Fld2)>0
begin
Set @FldTmp = @FldTmp + (select substring(@Fld2,1,charindex(',',@Fld2)-1) + ' as _' + substring(@Fld2,1,charindex(',',@Fld2)-1) + ',')
Set @Fld2 = (select ltrim(right(@Fld2,len(@Fld2)-charindex(',',@Fld2))))
end
Set @FldTmp = @FldTmp + @Fld2 + ' as _' + @Fld2
Set @Fld2 = @FldTmp
--Determinate primary key jointure
--rule: same pk in both tables
Set @nLoop = 1
Set @SQL = 'Declare crsr cursor for select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '''
+ @Tbl1 + ''' or TABLE_SCHEMA + ''.'' + TABLE_NAME = ''' + @Tbl1 + ''' or TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.'' + TABLE_NAME = ''' + @Tbl1
+ ''' order by ORDINAL_POSITION'
exec(@SQL)
open crsr
fetch next from crsr into @strTmp
while @@fetch_status = 0
begin
if @nLoop = 1
begin
Set @Pk = 's.' + @strTmp + ' = b._' + @strTmp
Set @Pk1 = @strTmp
set @nLoop = @nLoop + 1
end
Else
Set @Pk = @Pk + ' and s.' + @strTmp + ' = b._' + @strTmp
fetch next from crsr into @strTmp
end
close crsr
deallocate crsr
--SQL statement build
set @SQL = 'select case when s.' + @Pk1 + ' is null then ''Del'' when b._' + @Pk1 + ' is null then ''Add'' else ''Gap'' end as TypErr, '''
set @SQL = @SQL + @Tbl1 +''' as Tbl1, s.*, ''' + @Tbl2 +''' as Tbl2 ,b.* from (Select ' + @Fld1 + ' from ' + @Tbl1
set @SQL = @SQL + ' EXCEPT SELECT ' + @Fld2 + ' from ' + @Tbl2 + ')s full join (Select ' + @Fld2 + ' from ' + @Tbl2
set @SQL = @SQL + ' EXCEPT SELECT ' + @Fld1 + ' from ' + @Tbl1 +')b on '+ @Pk
--Run SQL statement
Exec(@SQL)
END
回答by IanMc
Enhancement to dietbuddha's answer...
增强dietbuddha的答案...
select * from
(
select * from tableA
minus
select * from tableB
)
union all
select * from
(
select * from tableB
minus
select * from tableA
)
回答by user3665906
You can find differences of 2 tables using combination of insert all and full outer join in Oracle. In sql you can extract the differences via full outer join but it seems that insert all/first doesnt exist in sql! Hence, you have to use following query instead:
您可以在 Oracle 中使用 insert all 和 full outer join 的组合找到 2 个表的差异。在 sql 中，您可以通过完全外连接提取差异，但似乎 sql 中不存在 insert all/first！因此，您必须改用以下查询：
select * from A
full outer join B on
A.pk=B.pk
where A.field1!=B.field1
or A.field2!=B.field2 or A.field3!=B.field3 or A.field4!=B.field4
--and A.Date==Date1
Although using 'OR' in where clause is not recommended and it usually yields in lower performance, you can still use above query if your tables are not massive. If there is any result for the above query, it is exactly the differences of 2 tables based on comparison of fields 1,2,3,4. For improving the query performance, you can filter it by date as well(check the commented part)
尽管不建议在 where 子句中使用“OR”，并且它通常会降低性能，但如果您的表不是很大，您仍然可以使用上述查询。如果上面的查询有任何结果，正是基于字段1、2、3、4比较的2个表的差异。为了提高查询性能，您也可以按日期过滤（检查注释部分）
回答by Madhushankar MJ
We can compare data from two tables of DB2 tables using the below simple query,
我们可以使用下面的简单查询比较 DB2 表的两个表中的数据，
Step 1:- Select which all columns we need to compare from table (T1) of schema(S)
第 1 步：- 从 schema(S) 的表 (T1) 中选择我们需要比较的所有列
SELECT T1.col1,T1.col3,T1.col5 from S.T1
Step 2:- Use 'Minus' keyword for comparing 2 tables.
第 2 步：- 使用“减号”关键字比较 2 个表。
Step 3:- Select which all columns we need to compare from table (T2) of schema(S)
第 3 步：- 从 schema(S) 的表 (T2) 中选择我们需要比较的所有列
SELECT T2.col1,T2.col3,T2.col5 from S.T1
END result:- SELECT T1.col1,T1.col3,T1.col5 from S.T1 MINUS SELECT T2.col1,T2.col3,T2.col5 from S.T1;
最终结果：- SELECT T1.col1,T1.col3,T1.col5 from S.T1 MINUS SELECT T2.col1,T2.col3,T2.col5 from S.T1;
If the query returns no rows then the data is exactly the same.
如果查询未返回任何行，则数据完全相同。
回答by Ludovic Aubert
To compare T1(PK, A, B) and T2(PK, A, B).
比较T1（PK，A，B）和T2（PK，A，B）。
First compare primary key sets to look for missing key values on either side:
首先比较主键集以查找任一侧缺失的键值：
SELECT T1.*, T2.* FROM T1 FULL OUTER JOIN T2 ON T1.PK=T2.PK WHERE T1.PK IS NULL OR T2.PK IS NULL;
Then list all value mismatch:
然后列出所有值不匹配：
SELECT T1.PK, 'A' AS columnName, T1.A AS leftValue, T2.A AS rightValue FROM T1 JOIN T2 ON T1.PK=T2.PK WHERE COALESCE(T1.A,0) != COALESCE(T2.A,0)
UNION ALL
SELECT T1.PK, 'B' AS columnName, T1.B AS leftValue, T2.B AS rightValue FROM T1 JOIN T2 ON T1.PK=T2.PK WHERE COALESCE(T1.B,0) != COALESCE(T2.B,0)
A and B must be of same type. You can use INFORMATION SCHEMA to generate the SELECT. Don't forget the COALESCE to also include IS NULL results. You could also use FULL OUTER JOIN and COALESCE(T1.PK,0)=COALESCE(T2.PK,0).
A 和 B 必须是同一类型。您可以使用 INFORMATION SCHEMA 来生成 SELECT。不要忘记 COALESCE 还包括 IS NULL 结果。您还可以使用 FULL OUTER JOIN 和 COALESCE(T1.PK,0)=COALESCE(T2.PK,0)。
For example for columns of type varchar:
例如对于 varchar 类型的列：
SELECT concat('SELECT T1.PK, ''', COLUMN_NAME, ''' AS columnName, T1.', COLUMN_NAME, ' AS leftValue, T2.', COLUMN_NAME, ' AS rightValue FROM T1 JOIN T2 ON T1.PK=T2.PK WHERE COALESCE(T1.',COLUMN_NAME, ',0)!=COALESCE(T2.', COLUMN_NAME, ',0)')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='T1' AND DATA_TYPE IN ('nvarchar','varchar');
