sqlserver 建视图添加一列自增ID

最新推荐文章于 2023-08-02 21:37:49 发布

原创最新推荐文章于 2023-08-02 21:37:49 发布 · 9.4k 阅读

3 ·

CC 4.0 BY-SA版权

sqlserver 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一个复杂的SQL视图创建过程，该视图整合了多个表的数据，并通过LEFT OUTER JOIN进行关联，确保即使某些关联表中没有对应记录也能返回基表的所有记录。视图还使用了CASE WHEN逻辑来处理空值或空白值的情况，并通过ROW_NUMBER()函数为每条记录生成唯一的自增长ID。

CREATE VIEW [dbo].[BaseOrganization_v]
AS
SELECT     ROW_NUMBER() OVER ( ORDER BY pob.PropertyId ASC ) AS id,     --------基表没有id列，取下一列rownum做自增id
pob.PropertyId, (CASE WHEN pd_1.PropertyValue IS NULL OR
                      pd_1.PropertyValue = '' THEN bvl_1.Value ELSE pd_1.PropertyValue END ) COLLATE Chinese_PRC_CI_AS AS PropertyValue, pob.ParentPropertyId, ISNULL(pd_2.PropertyValue, bvl_2.Value)
                      AS ParentPropertyValue, pob.LevelId, bvs_1.ValueSetName AS LevelValue, pob.Sequence, pob.OrganizationTypeId, pot.TypeName AS OrganizationType,
                      pob.PropertyTypeId
FROM         EDP_Privilege.PropertyOrganizationBase AS pob LEFT OUTER JOIN
                      EDP_Privilege.PropertyDetail AS pd_1 ON pob.PropertyId = pd_1.PropertyId LEFT OUTER JOIN
                      EDP_Utility.BaseValueList AS bvl_1 ON ISNULL(pd_1.BaseValueListId, 0) = bvl_1.BaseValueListId LEFT OUTER JOIN
                      EDP_Privilege.PropertyDetail AS pd_2 ON pob.ParentPropertyId = pd_2.PropertyId LEFT OUTER JOIN
                      EDP_Utility.BaseValueList AS bvl_2 ON ISNULL(pd_2.BaseValueListId, 0) = bvl_2.BaseValueListId LEFT OUTER JOIN
                      EDP_Privilege.PropertyOrganizationLevel AS pol ON pob.LevelId = pol.OrganizationLevelId LEFT OUTER JOIN
                      EDP_Privilege.PropertyOrganizationType AS pot ON pob.OrganizationTypeId = pot.OrganizationTypeId LEFT OUTER JOIN
                      EDP_Utility.BaseValueSet AS bvs_1 ON pol.BaseValueSetId = bvs_1.BaseValueSetId