sql 创建动态视图，动态列

最新推荐文章于 2022-05-22 11:01:43 发布

原创最新推荐文章于 2022-05-22 11:01:43 发布 · 4.7k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #null #timezone #table #function #脚本

本文介绍了如何在SQL中创建动态视图，针对不同的时区选择，动态选取开始和结束时间列。首先创建了一个包含多个时区时间的表格，然后定义了一个函数fnTestView，该函数根据传入的时区参数返回相应的开始和结束时间。

1 第一步(生成时区表)
USE [Elian]
GO
/****** 对象: Table [dbo].[G_LessonTimeZone]    脚本日期: 09/02/2008 11:46:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[G_LessonTimeZone](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [lessonid] [int] NOT NULL,
    [LessonStartTimeZoneEast1] [datetime] NULL,
    [LessonStartTimeZoneEast2] [datetime] NULL,
    [LessonStartTimeZoneEast3] [datetime] NULL,
    [LessonStartTimeZoneEast4] [datetime] NULL,
    [LessonStartTimeZoneEast5] [datetime] NULL,
    [LessonStartTimeZoneEast6] [datetime] NULL,
    [LessonStartTimeZoneEast7] [datetime] NULL,
    [LessonStartTimeZoneEast8] [datetime] NULL,
    [LessonStartTimeZoneEast9] [datetime] NULL,
    [LessonStartTimeZoneEast10] [datetime] NULL,
    [LessonStartTimeZoneEast11] [datetime] NULL,
    [LessonStartTimeZoneEast12] [datetime] NULL,
    [LessonStartTimeZoneWest1] [datetime] NULL,
    [LessonStartTimeZoneWest2] [datetime] NULL,
    [LessonStartTimeZoneWest3] [datetime] NULL,
    [LessonStartTimeZoneWest4] [datetime] NULL,
    [LessonStartTimeZoneWest5] [datetime] NULL,
    [LessonStartTimeZoneWest6] [datetime] NULL,
    [LessonStartTimeZoneWest7] [datetime] NULL,
    [LessonStartTimeZoneWest8] [datetime] NULL,
    [LessonStartTimeZoneWest9] [datetime] NULL,
    [LessonStartTimeZoneWest10] [datetime] NULL,
    [LessonStartTimeZoneWest11] [datetime] NULL,
    [LessonStartTimeZoneWest12] [datetime] NULL,
    [LessonEndTimeZoneEast1] [datetime] NULL,
    [LessonEndTimeZoneEast2] [datetime] NULL,
    [LessonEndTimeZoneEast3] [datetime] NULL,
    [LessonEndTimeZoneEast4] [datetime] NULL,
    [LessonEndTimeZoneEast5] [datetime] NULL,
    [LessonEndTimeZoneEast6] [datetime] NULL,
    [LessonEndTimeZoneEast7] [datetime] NULL,
    [LessonEndTimeZoneEast8] [datetime] NULL,
    [LessonEndTimeZoneEast9] [datetime] NULL,
    [LessonEndTimeZoneEast10] [datetime] NULL,
    [LessonEndTimeZoneEast11] [datetime] NULL,
    [LessonEndTimeZoneEast12] [datetime] NULL,
    [LessonEndTimeZoneWest1] [datetime] NULL,
    [LessonEndTimeZoneWest2] [datetime] NULL,
    [LessonEndTimeZoneWest3] [datetime] NULL,
    [LessonEndTimeZoneWest4] [datetime] NULL,
    [LessonEndTimeZoneWest5] [datetime] NULL,
    [LessonEndTimeZoneWest6] [datetime] NULL,
    [LessonEndTimeZoneWest7] [datetime] NULL,
    [LessonEndTimeZoneWest8] [datetime] NULL,
    [LessonEndTimeZoneWest9] [datetime] NULL,
    [LessonEndTimeZoneWest10] [datetime] NULL,
    [LessonEndTimeZoneWest11] [datetime] NULL,
    [LessonEndTimeZoneWest12] [datetime] NULL,
CONSTRAINT [PK_G_LessonTimeZone] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

2 第二步根据选择时区生成动态视图，生态取列
    CREATE FUNCTION fnTestView (@timezone int)
        RETURNS TABLE
        AS
        RETURN (select lessonid,lessonstart=(case @timezone
                   when 1 then lessonstarttimezoneeast1
                   when 2 then lessonstarttimezoneeast2
                   when 3 then lessonstarttimezoneeast3
                   when 4 then lessonstarttimezoneeast4
                   when 5 then lessonstarttimezoneeast5
                   when 6 then lessonstarttimezoneeast6
                   when 7 then lessonstarttimezoneeast7
                   when 8 then lessonstarttimezoneeast8
                   when 9 then lessonstarttimezoneeast9
                   when 10 then lessonstarttimezoneeast10
                   when 11 then lessonstarttimezoneeast11
                   when 12 then lessonstarttimezoneeast12
                   when -1 then lessonstarttimezonewest1
                   when -2 then lessonstarttimezonewest2
                   when -3 then lessonstarttimezonewest3
                   when -4 then lessonstarttimezonewest4
                   when -5 then lessonstarttimezonewest5
                   when -6 then lessonstarttimezonewest6
                   when -7 then lessonstarttimezonewest7
                   when -8 then lessonstarttimezonewest8
                   when -9 then lessonstarttimezonewest9
                   when -10 then lessonstarttimezonewest10
                   when -11 then lessonstarttimezonewest11
                   when -12 then lessonstarttimezonewest12 end),
                   lessonend=(case @timezone
                   when 1 then lessonendtimezoneeast1
                   when 2 then lessonendtimezoneeast2
                   when 3 then lessonendtimezoneeast3
                   when 4 then lessonendtimezoneeast4
                   when 5 then lessonendtimezoneeast5
                   when 6 then lessonendtimezoneeast6
                   when 7 then lessonendtimezoneeast7
                   when 8 then lessonendtimezoneeast8
                   when 9 then lessonendtimezoneeast9
                   when 10 then lessonendtimezoneeast10
                   when 11 then lessonendtimezoneeast11
                   when 12 then lessonendtimezoneeast12
                   when -1 then lessonendtimezonewest1
                   when -2 then lessonendtimezonewest2
                   when -3 then lessonendtimezonewest3
                   when -4 then lessonendtimezonewest4
                   when -5 then lessonendtimezonewest5
                   when -6 then lessonendtimezonewest6
                   when -7 then lessonendtimezonewest7
                   when -8 then lessonendtimezonewest8
                   when -9 then lessonendtimezonewest9
                   when -10 then lessonendtimezonewest10
                   when -11 then lessonendtimezonewest11
                   when -12 then lessonendtimezonewest12 end
                   ) from testtimezone)

3 使用
select * from fnTestView(5)