Question

数据库的联合查询，两张表，表A跟表B字段名不同，部分数据相同。

想要全部显示表A跟表B的数据，但又要避免笛卡尔积

Answer 1

CREATE TABLE A(a1 INT,a2 INT ,a3 INT ,a10 INT )
CREATE TABLE b (a2 INT ,a3 INT,a10 INT ,a9 INT ,a5 INT )
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',' + 'isnull('+QUOTENAME(name)+','''')'
FROM syscolumns
WHERE id = OBJECT_ID('A')
AND name IN ( SELECT NAME FROM sys.columns WHERE object_id=OBJECT_ID('A')
INTERSECT
SELECT NAME FROM sys.columns WHERE object_id=OBJECT_ID('B') )
SET @s = STUFF(@s, 1, 1, '')
EXEC ('select '+@s+' from A ')