是否可以查询select报告并将输出加入另一个select语句中？

Question

我在SQL中有一个枢轴类型的报告(创建为视图)。

票号是行，而票证状态是列。

数据中的值是票在这种状态下所花时间的时间戳(hh:mm:ss)。

SQL视图示例(注释:未列出所有状态(请参见SQL查询中的所有)：

ticket_number   closed    completed   new       waiting-1   
22              0:00:00   0:00:00     0:00:00   0:00:00
32              0:00:00   0:00:00     6:00:01   0:00:00 

SQL数据透视查询：

v_pivottime

WITH t AS (
    select 
        ticket_number,
        ISNULL(status,'null') status,
        update_date,
        row_number() OVER (PARTITION BY ticket_number ORDER BY update_date) rn
    from xxx.dbo.report
), s AS (
    SELECT 
        t1.ticket_number,
        t1.status,
        t1.update_date,
        t2.update_date prevdate,
        case when t2.status=t1.status then DATEDIFF(s, t2.update_date, t1.update_date) end dif
    FROM t t1
    LEFT JOIN t t2 ON t1.ticket_number=t2.ticket_number AND t1.rn=t2.rn+1
)
SELECT *
FROM (
    SELECT
        ticket_number,
        [status],
        cast(dateadd(s,sum(dif),0) as time(0)) [time]
    FROM s
    GROUP BY ticket_number,status )  src
PIVOT
(
  min([time])
  for [status] in ([Closed], [Complete], [New], [Waiting-1], [Waiting-2], [Waiting-3], [stoptime], [Research-1], [Research-2], [Resolved], [Special Request], [Opened Request], [null])
) piv;

查询视图(下面)我想添加(连接？)枢轴输出到顶部(列)并添加状态名称，以及hh:mm:ss中的值输出。

v_last_update

select
        ctr1.ticket_number,
        ctr1.status,
        ctr1.create_date,
        ctr1.created_by,
        ctr1.customer,
        ctr1.description,
        ctr1.update_sequence,
        ctr1.update_date        
FROM    
        xx.dbo.report ctr1
JOIN    (
                select ticket_number as ticket_number_d,
                MAX(update_date) as max_update_date,
                MAX(update_sequence) as max_ update_sequence
                from xx.dbo.report
                group by ticket_number
        ) ctr2

ON      ctr1.ticket_number = ctr2.ticket_number_d
AND     ctr1.update_date = ctr2.max_update_date
AND     ctr1.update_sequence = ctr2.max_update_sequence
WHERE   customer <> ‘Bread’
ORDER BY max_update_date ASC;

*希望了解是否有一种方法可以从v_last_update查询中的票号中执行vlookup类型处理，并将列添加到输出中列的末尾。希望为每个状态添加一个列，并插入该票证状态的时间。这有可能把我逼疯了！

谢谢!

-P

Answer 1

除了我的评论外，您还可以让数据透视部分成为CTE：

(未经测试)

WITH t AS (
    select 
        ticket_number,
        ISNULL(status,'null') status,
        update_date,
        row_number() OVER (PARTITION BY ticket_number ORDER BY update_date) rn
    from xxx.dbo.report
), s AS (
    SELECT 
        t1.ticket_number,
        t1.status,
        t1.update_date,
        t2.update_date prevdate,
        case when t2.status=t1.status then DATEDIFF(s, t2.update_date, t1.update_date) end dif
    FROM t t1
    LEFT JOIN t t2 ON t1.ticket_number=t2.ticket_number AND t1.rn=t2.rn+1
)
,pivotPart AS
(
    SELECT *
    FROM (
        SELECT
            ticket_number,
            [status],
            cast(dateadd(s,sum(dif),0) as time(0)) [time]
        FROM s
        GROUP BY ticket_number,status )  src
    PIVOT
    (
      min([time])
      for [status] in ([Closed], [Complete], [New], [Waiting-1], [Waiting-2], [Waiting-3], [stoptime], [Research-1], [Research-2], [Resolved], [Special Request], [Opened Request], [null])
    ) piv
)
select
        ctr1.ticket_number,
        ctr1.status,
        ctr1.create_date,
        ctr1.created_by,
        ctr1.customer,
        ctr1.description,
        ctr1.update_sequence,
        ctr1.update_date,
        pivotPart.*        
FROM    
        xx.dbo.report ctr1
JOIN pivotPart ON ctr1.ticket_number=pivotPart.ticket_number
JOIN    (
                select ticket_number as ticket_number_d,
                MAX(update_date) as max_update_date,
                MAX(update_sequence) as max_ update_sequence
                from xx.dbo.report
                group by ticket_number
        ) ctr2

ON      ctr1.ticket_number = ctr2.ticket_number_d
AND     ctr1.update_date = ctr2.max_update_date
AND     ctr1.update_sequence = ctr2.max_update_sequence
WHERE   customer <> ‘Bread’
ORDER BY max_update_date ASC;

Answer 2

其实，这比我想象的要容易得多。

我所做的就是将select语句和枢轴查询转换成单独的视图。之后，将select视图与ticket_number上的支点视图连接起来。它以select语句票证号内联方式返回了pivot视图中的所有列。现场检查过了，点上了！

谢谢大家的帮助，非常感谢！