sql - SQL Server中如何在两列中显示一列值

Question

我想在两个不同的列中显示状态列值，如 oldstatus 和 newstatus 如此处所示。

表格:

CREATE TABLE #Data
(
      assetno VARCHAR(10),      
      status VARCHAR(30),
      statusid VARCHAR(10),
      cdate DATE
)

示例数据:

INSERT INTO #Data (assetno, status, statusid, cdate)
VALUES ('M1008', 'NOT READY', '302', '2019-06-03 18:53:51.643' ),
       ('M1008', 'INACTIVE', '302', '2019-06-03 18:56:07.83' ),
       ('M1008', 'EMREVIEW', '304', '2019-06-03 19:09:55.61' ),
       ('M1008', 'INACTIVE', '402', '2019-06-03 19:16:20.707'),
       ('M1008', 'EMREVIEW', '403', '2019-06-03 19:16:37.347'),
       ('M1008', 'NOT READY', '404', '2019-06-03 19:18:57.607'),
       ('M1008', 'EMREVIEW', '405', '2019-06-04 11:28:02.007'),
       ('M1008', 'OPERATING', '502', '2019-06-04 11:31:17.143'),    
       ('M1008', 'NOT READY', '802', '2019-06-06 11:03:32.14'),     
       ('M1008', 'INACTIVE', '603', '2019-06-04 11:37:10.117'),
       ('M1008', 'EMREVIEW', '3782', '2019-07-15 16:35:38.793'),        
       ('M1008', 'NOT READY', '3579', '2019-07-11 18:27:08.567'),
       ('M1008', 'OPERATING', '4178', '2019-07-18 22:07:18.513'),
       ('M1008', 'OPERATING', '2602', '2019-06-26 17:56:39.983');

输出需要如下所示:

assetno  oldstatus  newstatus                     cdate
-------  ---------  ---------                     -------
M1008   NOT READY   ---             2019-06-03 18:53:51.643         
M1008   NOT READY   INACTIVE        2019-06-03 18:56:07.83      
M1008   INACTIVE    EMREVIEW        2019-06-03 19:09:55.61      
M1008   EMREVIEW    INACTIVE        2019-06-03 19:16:20.707     
M1008   INACTIVE    EMREVIEW        2019-06-03 19:16:37.347     
M1008   EMREVIEW    NOT READY       2019-06-03 19:18:57.607     
M1008   NOT READY   EMREVIEW        2019-06-04 11:28:02.007     
M1008   EMREVIEW    OPERATING       2019-06-04 11:31:17.143     
M1008   OPERATING   NOT READY       2019-06-06 11:03:32.14      
M1008   NOT READY   INACTIVE        2019-06-04 11:37:10.117     
M1008   INACTIVE    EMREVIEW        2019-07-15 16:35:38.793     
M1008   EMREVIEW    NOT READY       2019-07-11 18:27:08.567     
M1008   NOT READY   OPERATING       2019-07-18 22:07:18.513     
M1008   OPERATING   OPERATING       2019-06-26 17:56:39.983

Answer 1

像这样的东西应该工作 - 如果您的 SQL Server 版本足够新(需要 SQL Server 2012 或更高版本):

SELECT  
    assetno,
    oldstatus = LAG(status, 1, '---') OVER (ORDER BY Cdate),
    newstatus = status,
    cdate       
FROM 
    #data
ORDER BY 
    cdate 

正如我在评论中所说 - 你真的应该为你的 CDate 列使用 DATETIME2(3) - 否则，如果有几行带有相同的日期(仅时间不同 - 但对于 DATE，时间将被丢弃而不存储)。

我得到这个输出: