sql - 基于条件的行号
问题描述
declare @delimiter nvarchar(2) = char(10);
declare @objectName sysname = 'dbo.table_1';
-----------------------------------------------------------------------------------------------
with CTE as (
select
0 as line_number
, @objectName as [object_name]
, ltrim(rtrim(object_definition( object_id(@objectName)))) as def
, convert(nvarchar(max), N'') as line
union all
select
line_number + 1
, @objectName as [object_name]
, substring(def, charindex(@delimiter, def) + len(@delimiter), len(def) - (charindex(@delimiter, def)))
, left(def, charindex(@delimiter, def)) as line
from
CTE
where
charindex(@delimiter, def) <> 0
)
select
--a.line_number
ROW_NUMBER() OVER (PARTITION BY a.[object_name] order by a.[line_number]) as [new_line_num]
,a.[object_name]
-----
,a.line
-----
,case
when a.line like '%openquery%' then 'OPENQUERY'
else ''
end as [open_query_label]
-----
,case
when a.line like '%openquery%' then ROW_NUMBER () OVER(partition by a.[object_name] Order by (select 1) )
else 0
end as [open_query_group]
from
CTE as a
where
a.line_number >= 1
OPTION (MAXRECURSION 0);
此查询产生以下输出：
new_line_num object_name line open_query_label open_query_group
147 dbo.table_1 ,max(case when quest...) 0
148 dbo.table_1 ,max(case when quest...) 0
149 dbo.table_1 ,max(case when quest...) 0
150 dbo.table_1 ,max(case when quest...) 0
我的最终目标是抓住OPENQUERY每个定义的所有部分。
我要这样做的方法是标记OPENQUERY开始的行，然后抓取接下来的 15 行。它不会是 100% 准确的，但会非常接近。
我希望输出看起来像这样：
new_line_num object_name line open_query_label open_query_group
142 dbo.table_name ,max(case when... 0
153 dbo.table_name OPENQUERY([whatever OPENQUERY 1
154 dbo.table_name 'select 2
取值 15后open_query_group，我希望它回到 0，除非OPENQUERYSQL 语句中有另一个。发生这种情况时，我希望OPEN_QUERY_GROUP数字从 1 开始。
关于如何操作 ROW_NUMBER 函数的任何建议？
解决方案
这是一个部分解决方案，我不能用你的 CTE 做任何事情，所以我做了一些虚拟数据。
WITH cte
AS
(SELECT
*
FROM (VALUES
(1, ''), (2, ''), (3, 'OPENQUERY'), (4, ''), (5, ''), (6, ''), (7, ''), (8, ''), (9, ''), (10, ''), (11, 'OPENQUERY')
, (12, ''), (13, ''), (14, ''), (15, ''), (16, ''), (17, ''), (18, ''), (19, ''), (20, ''), (21, ''), (22, '')
, (23, ''), (24, ''), (25, ''), (26, ''), (27, ''), (28, ''), (29, ''), (30, ''), (31, ''), (32, ''), (33, '')
, (34, ''), (35, ''), (36, 'OPENQUERY'), (37, ''), (38, ''), (39, ''), (40, ''), (41, ''), (41, '')) a (new_line_num, open_query_label))
诀窍是首先为每个 OPENQUERY 实例创建一个组。
我通过计算总和来做到这一点，将存在 OPENQUERY 的行值设置为 1，否则设置为 0。这形成了一组不同的组，1 pr OPENQUERY（加上第一个 openquery 之前的任何内容）
SELECT
new_line_num
,open_query_label
,SUM(IIF(open_query_label = 'OPENQUERY', 1, 0)) OVER (ORDER BY new_line_num) grp
FROM cte
这给了我们这个（部分结果）
+--------------+------------------+-----+
| new_line_num | open_query_label | grp |
+--------------+------------------+-----+
| 1 | | 0 |
| 2 | | 0 |
| 3 | OPENQUERY | 1 |
| 4 | | 1 |
| 5 | | 1 |
| 6 | | 1 |
| 7 | | 1 |
| 8 | | 1 |
| 9 | | 1 |
| 10 | | 1 |
| 11 | OPENQUERY | 2 |
| 12 | | 2 |
| 13 | | 2 |
| 14 | | 2 |
+--------------+------------------+-----+
现在我将它打包为 CTE2，然后我只使用 row_number 来获取组：
WITH cte
AS
(SELECT
*
FROM (VALUES
(1, ''), (2, ''), (3, 'OPENQUERY'), (4, ''), (5, ''), (6, ''), (7, ''), (8, ''), (9, ''), (10, ''), (11, 'OPENQUERY')
, (12, ''), (13, ''), (14, ''), (15, ''), (16, ''), (17, ''), (18, ''), (19, ''), (20, ''), (21, ''), (22, '')
, (23, ''), (24, ''), (25, ''), (26, ''), (27, ''), (28, ''), (29, ''), (30, ''), (31, ''), (32, ''), (33, '')
, (34, ''), (35, ''), (36, 'OPENQUERY'), (37, ''), (38, ''), (39, ''), (40, ''), (41, ''), (41, '')) a (new_line_num, open_query_label)),
cte2
AS
(SELECT
new_line_num
,open_query_label
,SUM(IIF(open_query_label = 'OPENQUERY', 1, 0)) OVER (ORDER BY new_line_num) grp
FROM cte)
SELECT
new_line_num
,open_query_label
,ROW_NUMBER() OVER (PARTITION BY grp ORDER BY new_line_num) open_query_group
FROM cte2
部分结果：
+--------------+------------------+------------------+
| new_line_num | open_query_label | open_query_group |
+--------------+------------------+------------------+
| 1 | | 1 |
| 2 | | 2 |
| 3 | OPENQUERY | 1 |
| 4 | | 2 |
| 5 | | 3 |
| 6 | | 4 |
| 7 | | 5 |
| 8 | | 6 |
| 9 | | 7 |
| 10 | | 8 |
| 11 | OPENQUERY | 1 |
| 12 | | 2 |
+--------------+------------------+------------------+
最后，我们希望第一行从 0 而不是 1 开始，并且 open_query_group 从 15 滚动到 0
SELECT
new_line_num
,open_query_label
,(ROW_NUMBER() OVER (PARTITION BY grp ORDER BY new_line_num)
- iif(grp=0,1,0))%16
open_query_group
FROM cte2
这会特别对待第一个 grp (0)，并使用模数 16 使行号从 15 翻转到 0。
最终结果：
+--------------+------------------+------------------+
| new_line_num | open_query_label | open_query_group |
+--------------+------------------+------------------+
| 1 | | 0 |
| 2 | | 1 |
| 3 | OPENQUERY | 1 |
| 4 | | 2 |
| 5 | | 3 |
| 6 | | 4 |
| 7 | | 5 |
| 8 | | 6 |
| 9 | | 7 |
| 10 | | 8 |
| 11 | OPENQUERY | 1 |
| 12 | | 2 |
| 13 | | 3 |
| 14 | | 4 |
| 15 | | 5 |
| 16 | | 6 |
| 17 | | 7 |
| 18 | | 8 |
| 19 | | 9 |
| 20 | | 10 |
| 21 | | 11 |
| 22 | | 12 |
| 23 | | 13 |
| 24 | | 14 |
| 25 | | 15 |
| 26 | | 0 |
| 27 | | 1 |
| 28 | | 2 |
| 29 | | 3 |
| 30 | | 4 |
| 31 | | 5 |
| 32 | | 6 |
| 33 | | 7 |
| 34 | | 8 |
| 35 | | 9 |
| 36 | OPENQUERY | 1 |
| 37 | | 2 |
| 38 | | 3 |
| 39 | | 4 |
| 40 | | 5 |
| 41 | | 6 |
| 41 | | 7 |
+--------------+------------------+------------------+
推荐阅读
- javascript - 为什么双击在 Iphone 上不起作用
- python - 为什么 time.sleep() 一次发生而不是一次发生？
- python - 如何缩进Python中for循环产生的打印输出？
- swift - Swiftui - 蓝牙低功耗状态始终未知
- php - 会话 [(null externalkey)] 不可用且不在最后 1000 个终止会话中。活动会话是[ext。键 2a7...]
- php - 检查 m3u8 播放列表是否可用于 cURL
- java - onDataChange() 两次返回值
- ios - 大约在 iOS 13.3 / XCode 11 / Swift 中，字符串中的符号发生了变化
- android - 不带“=”的改造查询参数
- c++ - 在 c++ 中与 alsa-lib 同时播放“.wav”音频的简单示例？