代码之家  ›  专栏  ›  技术社区  ›  SkyeBoniwell

将生成的列添加到SQL查询结果的结尾

sql-server-2012 tsql sql-server
  •  -1
  • SkyeBoniwell  · 技术社区  · 6 年前

    我正在尝试找出是否可以使用SQL生成一个人工列。

    考虑到下面的假数据,如何创建一个名为 GENERATED_SEQUENCE .

    规则如下:

    • 必须在同一课程(ID)中
    • 必须在同一日期(课程日期)
    • 每节课必须在30分钟内进行(课程开始时间和课程结束时间)

    代码: 

    select 
    111 As Id, 'Bio 101' As Course_Name, 
    '1/10/2016' AS Course_Date, 
    '09:00:00' AS Course_Start_Time,
    '09:45:00' AS Course_End_Time, 
    1 AS GENERATED_SEQUENCE

union all

select
    111 As Id, 'Bio 101' As Course_Name, 
    '1/10/2016' AS Course_Date, 
    '10:00:00' AS Course_Start_Time,  
    '010:45:00' AS Course_End_Time, 
    2 AS GENERATED_SEQUENCE 

union all

select
    111 As Id, 'Bio 101' As Course_Name, 
    '1/10/2016' AS Course_Date, 
    '11:05:00' AS Course_Start_Time, 
    '12:30:00' AS Course_End_Time, 
    3 AS GENERATED_SEQUENCE

union all

select
    431 As Id, 'Econ 101' As Course_Name, 
    '1/12/2016' AS Course_Date, 
    '11:00:00' AS Course_Start_Time,
    '12:45:00' AS Course_End_Time, 
    1 AS GENERATED_SEQUENCE 

union all

select
    111 As Id, 'Bio 101' As Course_Name, 
    '1/12/2016' AS Course_Date, 
    '1:00:00' AS Course_Start_Time, 
    '2:45:00' AS Course_End_Time, 
    1 AS GENERATED_SEQUENCE 

union all

select
    543 As Id, 'Eng 200' As Course_Name, 
    '1/13/2016' AS Course_Date, 
    '2:00:00' AS Course_Start_Time, 
    '2:45:00' AS Course_End_Time, 
    1 AS GENERATED_SEQUENCE 

union all

select
    543 As Id, 'Eng 200' As Course_Name, 
    '1/13/2016' AS Course_Date, 
    '2:55:00' AS Course_Start_Time, 
    '3:55:00' AS Course_End_Time, 
    2 AS GENERATED_SEQUENCE 

union all

select
    543 As Id, 'Eng 200' As Course_Name, 
    '1/14/2016' AS Course_Date, 
    '6:00:00' AS Course_Start_Time, 
    '8:15:00' AS Course_End_Time, 
    1 AS GENERATED_SEQUENCE

    这有可能吗?

    谢谢!

    2 回复  |  直到 6 年前
        1
  •  1
  •   tonyd    6 年前

    这里有一个选项,使用一个CTE来处理数据,还有一个rank()和lead()函数。 

    WITH cteCourse
AS (SELECT 111 AS Id,
           'Bio 101' AS Course_Name,
           '1/10/2016' AS Course_Date,
           '09:00:00' AS Course_Start_Time,
           '09:45:00' AS Course_End_Time
    UNION ALL
    SELECT 111 AS Id,
           'Bio 101' AS Course_Name,
           '1/10/2016' AS Course_Date,
           '10:00:00' AS Course_Start_Time,
           '10:45:00' AS Course_End_Time
    UNION ALL
    SELECT 111 AS Id,
           'Bio 101' AS Course_Name,
           '1/10/2016' AS Course_Date,
           '11:05:00' AS Course_Start_Time,
           '12:30:00' AS Course_End_Time
    UNION ALL
    SELECT 431 AS Id,
           'Econ 101' AS Course_Name,
           '1/12/2016' AS Course_Date,
           '11:00:00' AS Course_Start_Time,
           '12:45:00' AS Course_End_Time
    UNION ALL
    SELECT 111 AS Id,
           'Bio 101' AS Course_Name,
           '1/12/2016' AS Course_Date,
           '13:00:00' AS Course_Start_Time,
           '14:45:00' AS Course_End_Time
    UNION ALL
    SELECT 543 AS Id,
           'Eng 200' AS Course_Name,
           '1/13/2016' AS Course_Date,
           '14:00:00' AS Course_Start_Time,
           '14:45:00' AS Course_End_Time
    UNION ALL
    SELECT 543 AS Id,
           'Eng 200' AS Course_Name,
           '1/13/2016' AS Course_Date,
           '14:55:00' AS Course_Start_Time,
           '15:55:00' AS Course_End_Time
    UNION ALL
    SELECT 543 AS Id,
           'Eng 200' AS Course_Name,
           '1/14/2016' AS Course_Date,
           '18:00:00' AS Course_Start_Time,
           '20:15:00' AS Course_End_Time)
SELECT Id,
       Course_Name,
       Course_Date,
       Course_Start_Time,
       Course_End_Time,
       CASE
           WHEN Course_End_Time + '00:30:00' <= LEAD(Course_Start_Time, 1, 0) OVER (ORDER BY Course_Name,
                                                                                             Course_Date,
                                                                                             Course_Start_Time,
                                                                                             Course_End_Time
                                                                                   ) THEN
               RANK() OVER (PARTITION BY Id,
                                         Course_Date
                            ORDER BY Course_Start_Time,
                                     Course_End_Time
                           )
           ELSE
               1
       END AS GENERATED_SEQUENCE
FROM cteCourse;

    回答下面的问题,您可以将第二个查询转换为CTE并像表一样从中进行查询。例如: 

    ,cteGenSeq AS (
SELECT Id,
       Course_Name,
       Course_Date,
       Course_Start_Time,
       Course_End_Time,
       CASE
           WHEN Course_End_Time + '00:30:00' <= LEAD(Course_Start_Time, 1, 0) OVER (ORDER BY Course_Name,
                                                                                             Course_Date,
                                                                                             Course_Start_Time,
                                                                                             Course_End_Time
                                                                                   ) THEN
               RANK() OVER (PARTITION BY Id,
                                         Course_Date
                            ORDER BY Course_Start_Time,
                                     Course_End_Time
                           )
           ELSE
               1
       END AS GENERATED_SEQUENCE
FROM cteCourse
)

SELECT * FROM cteGenSeq
        2
  •  1
  •   Tim Mylott    6 年前

    这里有一个选择。

    子查询使用:

    • 课程结束日期的lag(),以便我们可以在datediff()中使用它来确定上一课程完成的分钟数。
    • 用一个案例陈述来评估我们是否在30分钟内成为一个名为[prevcoursewithin30]的新专栏

    从这里,您可以简单地使用row_number()窗口函数,并按ID、课程日期和我们的新[prevcoursewithin30]列进行分区,以获得新的[生成的序列]列。

    看看这个: 

    DECLARE @TestData TABLE
    (
        [Id] INT
      , [Course_Name] NVARCHAR(100)
      , [Course_Date] DATE
      , [Course_Start_Time] TIME
      , [Course_End_Time] TIME
    );

INSERT INTO @TestData (
                          [Id]
                        , [Course_Name]
                        , [Course_Date]
                        , [Course_Start_Time]
                        , [Course_End_Time]
                      )
            SELECT 111 AS [Id]
                 , 'Bio 101' AS [Course_Name]
                 , '2016-01-10' AS [Course_Date]
                 , '09:00:00' AS [Course_Start_Time]
                 , '09:45:00' AS [Course_End_Time]
            UNION ALL
            SELECT 111 AS [Id]
                 , 'Bio 101' AS [Course_Name]
                 , '2016-01-10' AS [Course_Date]
                 , '10:00:00' AS [Course_Start_Time]
                 , '10:45:00' AS [Course_End_Time]
            UNION ALL
            SELECT 111 AS [Id]
                 , 'Bio 101' AS [Course_Name]
                 , '2016-01-10' AS [Course_Date]
                 , '11:05:00' AS [Course_Start_Time]
                 , '12:30:00' AS [Course_End_Time]
            UNION ALL
            SELECT 431 AS [Id]
                 , 'Econ 101' AS [Course_Name]
                 , '2016-01-12' AS [Course_Date]
                 , '11:00:00' AS [Course_Start_Time]
                 , '12:45:00' AS [Course_End_Time]
            UNION ALL
            SELECT 111 AS [Id]
                 , 'Bio 101' AS [Course_Name]
                 , '2016-01-12' AS [Course_Date]
                 , '1:00:00' AS [Course_Start_Time]
                 , '2:45:00' AS [Course_End_Time]
            UNION ALL
            SELECT 543 AS [Id]
                 , 'Eng 200' AS [Course_Name]
                 , '2016-01-13' AS [Course_Date]
                 , '2:00:00' AS [Course_Start_Time]
                 , '2:45:00' AS [Course_End_Time]
            UNION ALL
            SELECT 543 AS [Id]
                 , 'Eng 200' AS [Course_Name]
                 , '2016-01-13' AS [Course_Date]
                 , '2:55:00' AS [Course_Start_Time]
                 , '3:55:00' AS [Course_End_Time]
            UNION ALL
            SELECT 543 AS [Id]
                 , 'Eng 200' AS [Course_Name]
                 , '2016-01-14' AS [Course_Date]
                 , '6:00:00' AS [Course_Start_Time]
                 , '8:15:00' AS [Course_End_Time];


SELECT *
     , ROW_NUMBER() OVER ( PARTITION BY [CS].[Id]
                                      , [CS].[Course_Date]
                                      , [CS].[PrevCourseWithIn30]
                           ORDER BY [CS].[Course_Start_Time]
                         ) AS [GENERATED_SEQUENCE]
FROM   (
           SELECT *
                , CASE WHEN DATEDIFF(
                                        MINUTE
                                      , LAG(
                                              [Course_End_Time]
                                            , 1
                                            , [Course_Start_Time]
                                          ) OVER ( PARTITION BY [Id]
                                                              , [Course_Date]
                                                   ORDER BY [Course_Start_Time]
                                                 )
                                      , [Course_Start_Time]
                                    ) <= 30 THEN 1
                       ELSE 0
                  END AS [PrevCourseWithIn30]
           FROM   @TestData
       ) AS [CS];
    推荐文章
    M. Rictusgrin  ·  逐字符比较两个字符串并突出显示不同的字符串
    2 年前
    selva raj  ·  如何在sql server中创建具有动态列的表
    2 年前
    Sai  ·  如何恢复SQL Server 2012数据库?
    6 年前
    Masoud  ·  SQL Server中多列的透视
    6 年前
    Mitesh Jain  ·  如何消除重复记录
    6 年前
    ravsun  ·  按多列值自定义联合查询顺序
    6 年前
    NoBullMan  ·  SQL查询-删除列值重复的行
    6 年前
    Sai  ·  如何从SQL Server 2012中选择datetime where isnull值?
    6 年前
    JohnMark Sill  ·  通过一个条件将子集合从一行求和到另一行
    6 年前
    Malik Mahsam Awan  ·  从webapi获取角度4中的日期
    6 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号