在基于表的视图上合并所有视图
|
我有一个带有SQL视图列表的数据库表
我想创建一个新的视图或存储过程，基于该表中的视图将返回那些合并的视图
像这样
SELECT ALINE1, HOME, EMAIL, EXPIRE, EDATE7, type
FROM dbo.campaign_membership_30
UNION ALL
SELECT ALINE1, HOME, EMAIL, EXPIRE, EDATE7, type
FROM dbo.campaign_membership_30n
UNION ALL
SELECT ALINE1, HOME, EMAIL, EXPIRE, EDATE7, type
FROM dbo.campaign_membership_60n
UNION ALL
SELECT ALINE1, HOME, EMAIL, EXPIRE, EDATE7, type
FROM dbo.campaign_membership_today
UNION ALL
SELECT ALINE1, BOOKNO, EMAIL, DEPART, DEP7, type
FROM dbo.depart_151days
UNION ALL
SELECT ALINE1, BOOKNO, EMAIL, DEPART, DEP7, type
FROM dbo.depart_90Days
没有找到相关结果
已邀请:
2 个回复
岭取
DECLARE @sql VARCHAR(MAX) -- NVARCHAR if you use unicode characters in table names SELECT @sql = dbo.Concatenated_Rows(\'SELECT col1, col2 FROM \' + MT.table_name + \' UNION ALL \') FROM dbo.My_Table MT -- Remove the extra UNION ALL SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 11) EXEC(@sql)蓟类
您的查询将返回这三个视图的并集；正确？ 注意事项：我高度质疑要求您执行此操作的基础关系结构。更好的数据模型可能会使其不必要。但是我假设您没有更改模型的能力，因此请尝试以下操作： 假定上面列出的三个视图位于一个名为“ ViewsToReturn”的表的一个名为“ ViewName”的字段中。构建一个查询，该查询首先合并所有视图，然后根据ViewsToReturn中视图的名称对其进行过滤。像这样：SELECT * FROM (SELECT Field1, Field2, \'dbo.View1\' AS ViewName FROM dbo.View1 UNION SELECT Field1, Field2, \'dbo.View2\' AS ViewName FROM dbo.View2 UNION SELECT Field1, Field2, \'dbo.View3\' AS ViewName FROM dbo.View3 UNION SELECT Field1, Field2, \'dbo.View4\' AS ViewName FROM dbo.View4) AllRecords WHERE AllRecords.TableName IN SELECT ViewName FROM ViewsToReturn