根据两个独立表和数据库中的条件选择列名

Question

我在两个独立的数据库中有相同的表(架构可能不同，所以我使用"intersect"(：

• 数据库1中的MyTable
• 数据库2中的MyTable

当当前列的行在Database1中为NULL值而在Database2中为NOT NULL时，我想获取列名，如果可能的话，还想获取数据。

查询可以如下所示：

SELECT (
SELECT COLUMN_NAME FROM Database1.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='MyTable' AND TABLE_SCHEMA='dbo'
INTERSECT
SELECT COLUMN_NAME FROM Database2.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='MyTable' AND TABLE_SCHEMA='dbo'
) /* +data ? */
FROM Database1.dbo.MyTable AS MyTable1 INNER JOIN
Database2.dbo.MyTable AS MyTable2 ON MyTable1.id = MyTable2.id
WHERE MyTable1.COLUMN_NAME IS NULL AND MyTable2.COLUMN_NAME IS NOT NULL
OR MyTable2.COLUMN_NAME IS NULL AND MyTable1.COLUMN_NAME IS NOT NULL

但这显然是错误的。

Answer 1

您需要动态SQL来实现这一点，但它并不漂亮。

DECLARE @sql     nvarchar(max) = N'SELECT t1.id',
@sel     nvarchar(max) = N'', 
@clauses nvarchar(max) = N'';

;WITH src(col, name) AS
(
SELECT QUOTENAME(c.name), c.name 
FROM Database1.sys.columns AS c
INNER JOIN Database1.sys.tables AS t
ON c.[object_id] = t.[object_id]
WHERE t.name = N'MyTable' AND t.schema_id = 1
INTERSECT 
SELECT QUOTENAME(c.name), c.name 
FROM Database2.sys.columns AS c
INNER JOIN Database2.sys.tables AS t
ON c.[object_id] = t.[object_id]
WHERE t.name = N'MyTable' AND t.schema_id = 1
),
clauses(clause, sel) AS
(
SELECT clause = char(13) + char(10)
+ N'  OR (   (t1.' + col + N' IS NULL AND t2.' + col + N' IS NOT NULL)'
+ char(13) + char(10) 
+ '       OR (t1.' + col + N' IS NOT NULL AND t2.' + col + N' IS NULL))',
sel = N',' + char(13) + char(10) 
+ N'  t1_' + name + N' = t1.' + col 
+ N', t2_' + name + N' = t2.' + col
FROM src
WHERE name <> N'id'
)
SELECT @sel += sel, @clauses += clause FROM clauses;

SELECT @sql += @sel 
+ char(13) + char(10) + N' FROM Database1.dbo.MyTable AS t1'
+ char(13) + char(10) + N' INNER JOIN Database2.dbo.MyTable AS t2'
+ char(13) + char(10) + N' ON t1.id = t2.id AND (1 = 2' + @clauses + N');';

SELECT @sql;
--EXEC sys.sp_executesql @sql;

输出(假设MyTable具有公共列id、x和y(：

SELECT t1.id,
t1_x = t1.[x], t2_x = t2.[x],
t1_y = t1.[y], t2_y = t2.[y]
FROM Database1.dbo.MyTable AS t1
INNER JOIN Database2.dbo.MyTable AS t2
ON t1.id = t2.id AND (1 = 2
OR (   (t1.[x] IS NULL AND t2.[x] IS NOT NULL)
OR (t1.[x] IS NOT NULL AND t2.[x] IS NULL))
OR (   (t1.[y] IS NULL AND t2.[y] IS NOT NULL)
OR (t1.[y] IS NOT NULL AND t2.[y] IS NULL)));