如何选择*但不带“列名称在每个视图中必须是唯一的"； [英] How to SELECT * but without "Column names must be unique in each view"

问题描述

我需要封装一组表JOIN，这些表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

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