我需要一种使用SQL-Server来计数当前行之后具有特定类型的行数的方法，但是只能计数到特定类型的下一行，此时计数将重新开始。在下面的示例中，我需要将“子条款”之后的“子条款部分”或“时间表”之后的“时间表部分”计算在内。
示例数据：
Type Title
--------------------------------------------------
Clause 20
Sub-Clause 20.1 A Sub-Clause
Sub-Clause Section 20.1-1 A Sub-Clause Section
Schedule Schedule 1 to Blah
Schedule Section 1.0 X
Schedule Section 2.0 X
Schedule Schedule 2 to Blah
Sub-Clause 20.2 A Sub-Clause
Clause 21
Schedule Schedule 1 to Clause 21
Schedule Section 1.0 X
Schedule Section 2.0 X
我需要的输出将是这样的：
Type Title Count
---------------------------------------------------------------
Clause 20 0
Sub-Clause 20.1 A Sub-Clause 1
Schedule Schedule 1 to Blah 2
Schedule Schedule 2 to Blah 0
Sub-Clause 20.2 A Sub-Clause 0
Clause 21 0
Schedule Schedule 1 to Clause 21 2
我已经接近使用row_number（）标识每一行，然后计算出当前行与下一行之间的差异以及过滤掉标准行的解决方案。这是一种很粗糙的方法，我相信有更好的方法可以实现这一目标。另外，我的方法不适用于最后一行，因为它将为计数返回NULL。我的代码的简化版本是这样的：
SELECT
Type
, Title
, LEAD(myTable.Row#, 1) OVER(ORDER BY Row#) - Row# -1 AS 'Count'
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Row#
, Type
, Title
FROM Content
ORDER BY Row#
) AS myTable
WHERE myTable.Type in ('Clause', 'Sub-Clause', 'Schedule')
关于实现我所需要的更好方法的任何建议？
我做一些假设。有一个ID列可以定义行的顺序
-- Schema
declare @Content table
(
ID int identity,
Type varchar(20),
Title varchar(50)
)
-- Sample Data
insert into @Content
SELECT 'Clause', '20' union all
SELECT 'Sub-Clause', '20.1 A Sub-Clause' union all
SELECT 'Sub-Clause Section', '20.1-1 A Sub-Clause Section' union all
SELECT 'Schedule', 'Schedule 1 to Blah' union all
SELECT 'Schedule Section', '1.0 X' union all
SELECT 'Schedule Section', '2.0 X' union all
SELECT 'Schedule', 'Schedule 2 to Blah' union all
SELECT 'Sub-Clause', '20.2 A Sub-Clause' union all
SELECT 'Clause', '21' union all
SELECT 'Schedule', 'Schedule 1 to Clause 21' union all
SELECT 'Schedule Section', '1.0 X' union all
SELECT 'Schedule Section', '2.0 X'
-- Query
; with
cte as
(
-- grp is to identify group by rows with same Type
-- first set of `Schedule Section`
-- and last set of `Schedule Section` will have diff `grp`
SELECT *, grp = ID - row_number() over (PARTITION BY Type ORDER BY ID)
FROM @Content c
),
type_cnt as
(
-- count no of rows for each type
SELECT ID = min(ID), Type, cnt = count(*)
FROM cte c
WHERE c.Type not in ('Clause', 'Sub-Clause', 'Schedule')
GROUP BY Type, grp
)
SELECT c.ID, c.Type, c.Title, cnt = isnull(n.cnt, 0)
FROM cte c
outer apply
(
SELECT TOP 1 x.cnt
FROM type_cnt x
WHERE x.ID = c.ID + 1
) n
WHERE c.Type in ('Clause', 'Sub-Clause', 'Schedule')
ORDER BY ID
/* Result
1 Clause 20 0
2 Sub-Clause 20.1 A Sub-Clause 1
4 Schedule Schedule 1 to Blah 2
7 Schedule Schedule 2 to Blah 0
8 Sub-Clause 20.2 A Sub-Clause 0
9 Clause 21 0
10 Schedule Schedule 1 to Clause 21 2
*/
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句