时间戳

首页 > 解决方案 > 如何在 SQL 中连接未定义的连续行？

sql-server - 如何在 SQL 中连接未定义的连续行？

问题描述

我有下表

-- ----------------------------
-- Table structure for test
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type IN ('U'))
    DROP TABLE [dbo].[test]
GO

CREATE TABLE [dbo].[test] (
  [id] int  NOT NULL,
  [date] date  NOT NULL,
  [line] int  NOT NULL,
  [code] varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [description] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[test] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO [dbo].[test] ([id], [date], [line], [code], [description]) VALUES (N'100', N'2020-01-01', N'1', N'ABC', N'Test 123')
GO

INSERT INTO [dbo].[test] ([id], [date], [line], [code], [description]) VALUES (N'100', N'2020-01-01', N'2', N'DEF', N'Second Round')
GO

INSERT INTO [dbo].[test] ([id], [date], [line], [code], [description]) VALUES (N'100', N'2020-01-01', N'3', N'MIC', N'123')
GO

INSERT INTO [dbo].[test] ([id], [date], [line], [code], [description]) VALUES (N'100', N'2020-01-01', N'4', N'...', N'456')
GO

INSERT INTO [dbo].[test] ([id], [date], [line], [code], [description]) VALUES (N'100', N'2020-01-01', N'5', N'...', N'789')
GO

INSERT INTO [dbo].[test] ([id], [date], [line], [code], [description]) VALUES (N'100', N'2020-01-01', N'7', N'MID', N'TYest')
GO

INSERT INTO [dbo].[test] ([id], [date], [line], [code], [description]) VALUES (N'100', N'2020-01-01', N'8', N'...', N'1234567890')
GO

INSERT INTO [dbo].[test] ([id], [date], [line], [code], [description]) VALUES (N'100', N'2020-01-02', N'1', N'DEF', N'1234')
GO

INSERT INTO [dbo].[test] ([id], [date], [line], [code], [description]) VALUES (N'100', N'2020-01-02', N'2', N'...', N'5678')
GO

INSERT INTO [dbo].[test] ([id], [date], [line], [code], [description]) VALUES (N'100', N'2020-01-02', N'3', N'MIC', N'12345')
GO

INSERT INTO [dbo].[test] ([id], [date], [line], [code], [description]) VALUES (N'100', N'2020-01-02', N'4', N'...', N'67890')
GO

INSERT INTO [dbo].[test] ([id], [date], [line], [code], [description]) VALUES (N'101', N'2020-01-01', N'1', N'ABC', N'Test 234')
GO


-- ----------------------------
-- Primary Key structure for table test
-- ----------------------------
ALTER TABLE [dbo].[test] ADD CONSTRAINT [PK__test__642C6303AB3402F2] PRIMARY KEY CLUSTERED ([id], [date], [line])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

我想将描述与带有“...”的代码连接起来（续）这种连续性可以高达 90。

期望的输出

ID, Date, Line, Code, Description
----------------------------------
100, 2020-01-02, 1, DEF, 12345678
100, 2020-01-02, 3, MIC, 1234567890
100, 2020-01-01,3,  MIC, 123456789

等等。

我写了以下查询来完成任务

WITH CTE AS (SELECT  ID , DATE, LINE, CODE , 
    CASE 
        WHEN CODE <> '...' 
            AND LEAD( CODE) OVER (PARTITION BY (DATE) ORDER BY DATE) ='...'
            AND LEAD( CODE,2) OVER (PARTITION BY (DATE) ORDER BY DATE) <>'...'
             --CHECKS NEXT CONTINUATION
        THEN CONCAT(DESCRIPTION,
                LEAD( DESCRIPTION) OVER (ORDER BY DATE))
        WHEN CODE <> '...' 
            AND LEAD( CODE) OVER (PARTITION BY (DATE) ORDER BY DATE) ='...'
            AND LEAD( CODE,2) OVER (PARTITION BY (DATE) ORDER BY DATE) ='...'
            AND LEAD( CODE,3) OVER (PARTITION BY (DATE) ORDER BY DATE) <>'...'
             --CHECKS NEXT TWO CONTINUATION
        THEN CONCAT(DESCRIPTION,
                LEAD( DESCRIPTION) OVER (ORDER BY DATE),
                LEAD( DESCRIPTION,2) OVER (ORDER BY DATE))
    
    
        ELSE DESCRIPTION
    END AS NEXTDESC
    FROM TEST 
    )

SELECT * FROM CTE WHERE CODE <>'...'

但这仅涉及有限的连续性（最多 2 个，我们有多达 90 个）。我需要一个动态查询来完成这项工作，并且尽可能便宜。（一亿行）

我也在尝试一种不同的方法，如下所示

    ;with cte as(select ID, DATE, LINE, CODE, description, 
CASE WHEN
LEAD (CODE)OVER (ORDER BY DATE desc) = '...' 
THEN 1
ELSE 0
END Iscontd
    from test )

    select * from cte

这给出了以下输出

ID  DATE    LINE    CODE    description Iscontd
100 2020-01-02  1   DEF 1234    1
100 2020-01-02  2   ... 5678    0
100 2020-01-02  3   MIC 12345   1
100 2020-01-02  4   ... 67890   0
101 2020-01-01  1   ABC Test 234    0
100 2020-01-01  1   ABC Test 123    0
100 2020-01-01  2   DEF Second Round    0
100 2020-01-01  3   MIC 123 1
100 2020-01-01  4   ... 456 1
100 2020-01-01  5   ... 789 0
100 2020-01-01  7   MID TYest   1
100 2020-01-01  8   ... 1234567890  0

因此，只要该行继续，它将显示为 1，并且在代码中插入下一个值的位置它将变为 0

不确定这种方法是否会带我去任何地方。

标签： sql-servertsql

解决方案

--...一亿美元？？？？

select id, date, max(nullif(code, '...')) as code, codeline as line, string_agg(convert(varchar(max), description), '') within group (order by line) as descr
from
(
select *, max(case when code='...' then cast(null as int) else line end) over(partition by id, date order by line) as codeline
from dbo.test
) as src
group by id, date, codeline;

推荐阅读