SQL SQL在不同的表中查找相同的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6445573/
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 find the same column in different tables
提问by zmaster
I have 2 very large tables. I try to figure out what they have in common.
我有 2 张非常大的桌子。我试图找出他们的共同点。
They do not have the same numbers of columns. I could go about to just look at each column name from each table and compare - but they both have hundreds of columns (I have to do it for many such tables).
它们的列数不同。我可以只查看每个表中的每个列名并进行比较 - 但它们都有数百列（我必须为许多这样的表做这件事）。
I use MS Sql server.
我使用 MS Sql 服务器。
There are no constrains and no foregin keys on any of them.
它们中的任何一个都没有限制，也没有外键。
How do I go about doing that ?
我该怎么做？
Something like this:
像这样的东西：
select * AS "RES" from Table1 where RES IN (select * column from Table2)
Thanks in advance.
提前致谢。
回答by Jesse Hofmann
If you're looking for column names which are the same between two tables, this should work:
如果您正在寻找两个表之间相同的列名，这应该有效：
select name from syscolumns sc1 where id = object_id('table1') and exists(select 1 from syscolumns sc2 where sc2.name = sc1.name and sc2.id = object_id('table2'))
You could also make sure they're the same type by tossing in and sc1.xtype = sc2.xtype in the subquery.
您还可以通过在子查询中加入和 sc1.xtype = sc2.xtype 来确保它们是相同的类型。
回答by Adriano Carneiro
If I understood correctly, you are trying to compare the data in the two tables and check what the data has in common.
如果我理解正确，您正在尝试比较两个表中的数据并检查数据的共同点。
Provided that you have the columns you want to use for comparison (Table1.YourColumnand Table2.OtherColumn, in the example), you can do this:
如果您有要用于比较的列（Table1.YourColumn和Table2.OtherColumn，在示例中），您可以执行以下操作：
select YourColumn from Table1 t1
where exists (select OtherColumn
from Table2 t2
where t2.OtherColumn = t1.YourColumn)
回答by Vikram Singh
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX), @Table1 AS NVARCHAR(MAX)='Table1' , @Table2 AS NVARCHAR(MAX)='Table2'
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(A.COLUMN_NAME)
from INFORMATION_SCHEMA.COLUMNS A
join INFORMATION_SCHEMA.COLUMNS B
on A.COLUMN_NAME = B.COLUMN_NAME
where A.TABLE_NAME = @Table1
and B.TABLE_NAME = @Table2 and A.COLUMN_NAME not in ('Doc','CreatedBy')
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ' + @cols + '
from
(select A.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS A
join INFORMATION_SCHEMA.COLUMNS B
on A.COLUMN_NAME = B.COLUMN_NAME
where A.TABLE_NAME = '''+@Table1+'''
and B.TABLE_NAME = '''+@Table2+'''
) x
pivot
(
Max(COLUMN_NAME)
for COLUMN_NAME in (' + @cols + ')
) p '
execute sp_executesql @query
回答by itslittlejohn
Here is an SP to find common columns in two different tables..
这是一个 SP，用于在两个不同的表中查找公共列。
Works in SQL Server
在 SQL Server 中工作
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetColumnsData(@T_NAME1 varchar,@T_NAME2 varchar)
AS
BEGIN
DECLARE @Co int;
SET @co = 0;
CREATE TABLE #TEMP_TABLE(C_NAME VARCHAR(50),D_TYPE VARCHAR(50),T_NAME VARCHAR(50));
INSERT INTO #TEMP_TABLE (C_NAME,D_TYPE,T_NAME)( SELECT COLUMN_NAME,DATA_TYPE,
TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @T_NAME1 OR
TABLE_NAME= @T_NAME2);
SELECT @Co = COUNT(*) from #TEMP_TABLE t , #TEMP_TABLE t1 WHERE t1.C_NAME = t.C_NAME
and t.D_TYPE = t1.D_TYPE and t.T_NAME != t1.T_NAME
PRINT @co
END
回答by Jason
Assuming your RDBMS supports digests, you could calculate the digest of each row and join on the digest. Something like:
假设您的 RDBMS 支持摘要，您可以计算每一行的摘要并加入摘要。就像是：
SELECT T1.*
FROM
(SELECT *, MD5(col1, col2,...) as digest
FROM Table1) T1,
(SELECT *, MD5(col1, col2,...) as digest
FROM Table2) T2
WHERE T1.digest = T2.digest
I'm assuming that the two tables have the same columns and column types.
我假设这两个表具有相同的列和列类型。
