如何选择 * 但没有“列名在每个视图中必须唯一"

问题描述

我需要封装一组我们经常在供应商的数据库服务器上使用的表 JOIN.我们在提取等的许多地方重用了相同的 JOIN 逻辑，似乎 VIEW 允许在一个地方定义和维护 JOIN.

I need to encapsulate a set of tables JOINs that we freqently make use of on a vendor's database server. We reuse the same JOIN logic in many places in extracts etc. and it seemed a VIEW would allow the JOINs to be defined and maintained in one place.

CREATE VIEW MasterView
AS
SELECT *
FROM entity_1 e1
INNER JOIN entity_2 e2 ON e2.parent_id = entity_1.id
INNER JOIN entity_3 e3 ON e3.parent_id = entity_2.id
/* other joins including business logic */
etc.

问题在于供应商对数据库进行了定期更改(列添加、名称更改)，我希望这些更改自动反映在MasterView"中.

The trouble is that the vendor makes regular changes to the DB (column additions, name changes) and I want that to be reflected in the "MasterView" automatically.

SELECT * 允许这样做，但基础表都有 ID 列，所以我收到每个视图中的列名必须唯一"错误.

SELECT * would allow this, but the underlying tables all have ID columns so I get the "Column names in each view must be unique" error.

我特别想避免列出表中的列名，因为 a) 它需要频繁维护 b) 每个表有数百列.

I specifically want to avoid listing the column names from the tables because a) it requires frequent maintenance b) there are several hundred columns per table.

有什么方法可以实现 SELECT * 的动态但有效地排除某些列(即 ID 列)

Is there any way to achieve the dynamism of SELECT * but effectively exclude certain columns (i.e. the ID ones)

谢谢

推荐答案

我最终采用了这个方法，基于 Madhivanan 的建议.它类似于 t-clausen.dk 后来建议的(感谢您的努力)，尽管我发现 xml 路径样式比游标/等级分区更优雅.

I had gone with this in the end, building off of Madhivanan's suggestion. It's similar to what t-clausen.dk later suggested (thanks for your efforts) though I find the xml path style more elegant than cursors / rank partitions.

以下在运行时重新创建 MasterView 定义.基础表中的所有列都以表名开头，因此默认情况下我可以在视图中包含两个名称相似的列.仅此一项就解决了我原来的问题，但我还包含了WHERE column_name NOT IN"子句来专门排除某些永远不会在 MasterView 中使用的列.

The following recreates the MasterView definition when run. All columns in the underlying tables are prepended with the table name, so I can include two similarly named columns in the view by default. This alone solves my original problem, but I also included the "WHERE column_name NOT IN" clause to specifically exclude certain columns that will never be used in the MasterView.

create procedure Utility_RefreshMasterView 
as
begin

    declare @entity_columns varchar(max)
    declare @drop_view_sql varchar(max)
    declare @alter_view_definition_sql varchar(max)

    /* create comma separated string of columns from underlying tables aliased to avoid name collisions */
    select @entity_columns = stuff((
        select ','+table_name+'.['+column_name+'] AS ['+table_name+'_'+column_name+']' 
        from information_schema.columns
        where table_name IN ('entity_1', 'entity_2')
        and column_name not in ('column to exclude 1', 'column to exclude 2')
        for xml path('')), 1, 1, '')


    set @drop_view_sql = 'if exists (select * from sys.views where object_id = object_id(N''[dbo].[MasterView]'')) drop view MasterView'

    set @alter_view_definition_sql = 
    'create view MasterView as select ' + @entity_columns + '
    from entity_1
    inner join entity_2 on entity_2 .id = entity_1.id
    /* other joins follow */'

    exec (@drop_view_sql)
    exec (@alter_view_definition_sql)

end

这篇关于如何选择 * 但没有“列名在每个视图中必须唯一"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！