我在兩個單獨的資料庫中有同一個表（架構可以不同，所以我使用'intersect'）：
- 來自 Database1 的 MyTable
- 來自 Database2 的 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
但這顯然是錯誤的。
uj5u.com熱心網友回復：
為此，您需要動態 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)));
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/451908.html
標籤：sql sql服务器 数据库 sql-server-2008
上一篇：通過命令列將Unity專案匯出為AndroidStudio專案
下一篇：SQL-更多屬性的間隙和孤島問題