gpt4 book ai didi

sql - 高级 SQL Server 订购方式

转载 作者:行者123 更新时间:2023-12-03 14:35:01 26 4
gpt4 key购买 nike

我正在尝试使用 SQL 查询执行高级命令

我有一个用于跟踪工作流及其相关事件的数据库

之前的事件(已完成)在 closed 中状态。他们都有一个 Active_DateClosed_Date

当前事件在 active状态。有一个Active_Date设置,但 Closed_DateNULL
每个ECR.item_number active中将只有一项事件状态

我想订购

  • ECR.item_number但我希望订单基于 active 中的哪个事件按降序排列状态
  • A.ACTIVE_DATE

  • 询问:
    SELECT 
    ECR.item_number,
    ECR.title,
    convert(VARCHAR(10),ECR.CREATED_ON,120) AS [Date CR Created],
    convert(VARCHAR(10),A.ACTIVE_DATE,120) AS [Activity Activated],
    convert(VARCHAR(10),A.CLOSED_DATE,120) AS [Activity Closed],
    A.STATE,
    A.NAME AS [Activity Name],

    (CASE
    WHEN
    (A.STATE='Closed')
    THEN
    DATEDIFF(DAY, A.ACTIVE_DATE, A.CLOSED_DATE)
    END) AS [DAYS ACTIVITY (WAS) OPEN],

    (CASE
    WHEN
    (A.STATE='Active')
    THEN
    DATEDIFF(DAY, A.ACTIVE_DATE, Getdate())
    END) AS [DAYS ACTIVITY HAS BEEN OPEN]

    FROM
    innovator.ecr AS ECR
    INNER JOIN innovator.workflow AS WF
    ON CR.id = WF.source_id
    INNER JOIN innovator.workflow_process AS WFP
    ON WF.related_id = WFP.id
    INNER JOIN innovator.workflow_process_activity AS WPA
    ON WFP.id = WPA.source_id
    INNER JOIN innovator.activity AS A
    ON WPA.related_id = A.id

    --Shown for clarity
    ORDER BY
    ECR.ITEM_NUMBER,
    [Activity Activated]

    样本返回数据
    |-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|
    |CR Number | Title | Date CR Created | Activity Name | Activity Activated | Activity Closed | DAYS ACTIVITY (WAS) OPEN | DAYS ACTIVITY HAS BEEN OPEN |
    |-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|
    |CR-000119 | ITEM 1 | 4/12/2012 | Submit CR | 4/12/2012 | 31/01/2013 | 58 | 0 |
    |CR-000119 | ITEM 1 | 4/12/2012 | Check CR | 31/01/2013 | 13/02/2013 | 13 | 0 |
    |CR-000119 | ITEM 1 | 4/12/2012 | Review CR | 13/02/2013 | 21/02/2013 | 8 | 0 |
    |CR-000119 | ITEM 1 | 4/12/2012 | Technical Review | 21/02/2013 | 28/02/2013 | 7 | 0 |
    |CR-000119 | ITEM 1 | 4/12/2012 | CRB Meeting | 28/02/2013 | NULL | NULL | 11 |
    |CR-000123 | ITEM 2 | 6/12/2012 | Check CR | 6/12/2012 | 21/12/2012 | 15 | 0 |
    |CR-000123 | ITEM 2 | 6/12/2012 | Submit CR | 6/12/2012 | 6/12/2012 | 0 | 0 |
    |CR-000123 | ITEM 2 | 6/12/2012 | Review CR | 21/12/2012 | 17/01/2013 | 27 | 0 |
    |CR-000123 | ITEM 2 | 6/12/2012 | Technical Review | 17/01/2013 | 6/03/2013 | 48 | 0 |
    |CR-000123 | ITEM 2 | 6/12/2012 | CRB Meeting | 6/03/2013 | NULL | NULL | 5 |
    |CR-000136 | ITEM 3 | 11/01/2013 | Submit CR | 11/01/2013 | 15/01/2013 | 4 | 0 |
    |CR-000136 | ITEM 3 | 11/01/2013 | Check CR | 15/01/2013 | 16/01/2013 | 1 | 0 |
    |CR-000136 | ITEM 3 | 11/01/2013 | Review CR | 16/01/2013 | 21/01/2013 | 5 | 0 |
    |CR-000136 | ITEM 3 | 11/01/2013 | Technical Review | 21/01/2013 | 25/01/2013 | 4 | 0 |
    |CR-000136 | ITEM 3 | 11/01/2013 | CRB Meeting | 25/01/2013 | 31/01/2013 | 6 | 0 |
    |CR-000136 | ITEM 3 | 11/01/2013 | Technical Review | 31/01/2013 | 27/02/2013 | 27 | 0 |
    |CR-000136 | ITEM 3 | 11/01/2013 | CRB Meeting | 27/02/2013 | NULL | NULL | 12 |
    |-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|

    想要的结果
    |-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|
    |CR Number | Title | Date CR Created | Activity Name | Activity Activated | Activity Closed | DAYS ACTIVITY (WAS) OPEN | DAYS ACTIVITY HAS BEEN OPEN |
    |-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|
    |CR-000136 | ITEM 3 | 11/01/2013 | Submit CR | 11/01/2013 | 15/01/2013 | 4 | 0 |
    |CR-000136 | ITEM 3 | 11/01/2013 | Check CR | 15/01/2013 | 16/01/2013 | 1 | 0 |
    |CR-000136 | ITEM 3 | 11/01/2013 | Review CR | 16/01/2013 | 21/01/2013 | 5 | 0 |
    |CR-000136 | ITEM 3 | 11/01/2013 | Technical Review | 21/01/2013 | 25/01/2013 | 4 | 0 |
    |CR-000136 | ITEM 3 | 11/01/2013 | CRB Meeting | 25/01/2013 | 31/01/2013 | 6 | 0 |
    |CR-000136 | ITEM 3 | 11/01/2013 | Technical Review | 31/01/2013 | 27/02/2013 | 27 | 0 |
    |CR-000136 | ITEM 3 | 11/01/2013 | CRB Meeting | 27/02/2013 | NULL | NULL | 12 |
    |CR-000119 | ITEM 1 | 4/12/2012 | Submit CR | 4/12/2012 | 31/01/2013 | 58 | 0 |
    |CR-000119 | ITEM 1 | 4/12/2012 | Check CR | 31/01/2013 | 13/02/2013 | 13 | 0 |
    |CR-000119 | ITEM 1 | 4/12/2012 | Review CR | 13/02/2013 | 21/02/2013 | 8 | 0 |
    |CR-000119 | ITEM 1 | 4/12/2012 | Technical Review | 21/02/2013 | 28/02/2013 | 7 | 0 |
    |CR-000119 | ITEM 1 | 4/12/2012 | CRB Meeting | 28/02/2013 | NULL | NULL | 11 |
    |CR-000123 | ITEM 2 | 6/12/2012 | Check CR | 6/12/2012 | 21/12/2012 | 15 | 0 |
    |CR-000123 | ITEM 2 | 6/12/2012 | Submit CR | 6/12/2012 | 6/12/2012 | 0 | 0 |
    |CR-000123 | ITEM 2 | 6/12/2012 | Review CR | 21/12/2012 | 17/01/2013 | 27 | 0 |
    |CR-000123 | ITEM 2 | 6/12/2012 | Technical Review | 17/01/2013 | 6/03/2013 | 48 | 0 |
    |CR-000123 | ITEM 2 | 6/12/2012 | CRB Meeting | 6/03/2013 | NULL | NULL | 5 |
    |-----------+-----------+-------------------+-----------------------+-----------------------+-------------------+-------------------------------+-------------------------------|

    最佳答案

    这样做的关键是获取 Active 的日期每个项目的事件。这可以使用解析函数来完成:

    [DateOfActiveActivity] = MIN(CASE WHEN A.CLOSED_DATE IS NULL THEN A.ACTIVE_DATE END) OVER(PARTITION BY ECR.item_number)

    因此,要将其集成到您的查询中,您可以使用:
    WITH CTE AS
    ( SELECT [CRNumber] = ECR.item_number,
    ECR.title,
    [DateCRCreated] = CAST(ECR.CREATED_ON AS DATE),
    [ActivityActivated] = CAST(A.ACTIVE_DATE AS DATE),
    [ActivityClosed] = CAST(A.CLOSED_DATE AS DATE),
    A.STATE,
    [ActivityName] = A.NAME,
    [DAYSACTIVITYOPEN] = CASE WHEN A.STATE = 'Closed' THEN DATEDIFF(DAY, A.ACTIVE_DATE, A.CLOSED_DATE) END,
    [DAYSACTIVITYHASBEENOPEN] = CASE WHEN A.STATE = 'Active' THEN DATEDIFF(DAY, A.ACTIVE_DATE, GETDATE()) END,
    [DateOfActiveActivity] = MIN(CASE WHEN A.CLOSED_DATE IS NULL THEN A.ACTIVE_DATE END) OVER(PARTITION BY ECR.item_number)
    FROM innovator.ecr AS ECR
    INNER JOIN innovator.workflow AS WF
    ON CR.id = WF.source_id
    INNER JOIN innovator.workflow_process AS WFP
    ON WF.related_id = WFP.id
    INNER JOIN innovator.workflow_process_activity AS WPA
    ON WFP.id = WPA.source_id
    INNER JOIN innovator.activity AS A
    ON WPA.related_id = A.id
    )
    SELECT [CR Number] = [CRNumber],
    Title,
    [Date CR Created] = CONVERT(VARCHAR(10), DateCRCreated, 120),
    [Activity Activated] = CONVERT(VARCHAR(10), ActivityActivated, 120),
    [Activity Closed] = CONVERT(VARCHAR(10), ActivityClosed, 120),
    [STATE],
    [Activity Name] = ActivityName,
    [DAYS ACTIVITY (WAS) OPEN] = [DAYSACTIVITYOPEN],
    [DAYS ACTIVITY HAS BEEN OPEN] = [DAYSACTIVITYHASBEENOPEN]
    FROM CTE
    ORDER BY DateOfActiveActivity ASC, ActivityActivated, ActivityClosed;

    Demo with sample data on SQL Fiddle

    关于sql - 高级 SQL Server 订购方式,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/15333227/

    26 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com