22,209
社区成员
发帖
与我相关
我的任务
分享CTE递归只取第一层的一条数据(关键是只取一条数据想不到怎么操作)
;with t as (
select 1 as id, null as pid, value = 1.1
union all
select 2 as id, 1 as pid, value = 1.3
union all
select 2 as id, 1 as pid, value = 1.0
union all
select 2 as id, 1 as pid, value = 2.1
union all
select 2 as id, 1 as pid, value = 2.9
union all
select 2 as id, 1 as pid, value = 0.9
union all
select 7 as id, 2 as pid, value = 2.1
union all
select 7 as id, 2 as pid, value = 2.9
union all
select 7 as id, 2 as pid, value = 0.9
union all
select 3 as id, null as pid, value = 2.1
union all
select 5 as id, 3 as pid, value = 3.3
union all
select 5 as id, 3 as pid, value = 1.0
union all
select 5 as id, 3 as pid, value = 9.1
union all
select 5 as id, 3 as pid, value = 2.9
union all
select 5 as id, 3 as pid, value = 0.9
)
,vs as (
select * from t where pid is null
union all
select a.* from t a inner join vs b on a.pid = b.id where b.value < a.value and b.pid is null
)select * from vs
--RESULT
id pid value
--1 NULL 1.1
--3 NULL 2.1
--5 3 3.3
--2 1 1.3
如题，取截图的结果任意一条数据(图中蓝色背景的1，2，3，4，5，6，7，8)，不一定是3，6编号的数据，只要任意一条，因为数据量大，如果全部数据取出来再取任意一条数据太费时间，又不想写游标和分步处理(先临时表再一条条处理)，能否用CTE语法一条语句到位？
...全文
请发表友善的回复…
发表回复
freetd 2019-03-01
- 打赏
- 举报
怪我没有说清楚，关键是递归哪里的数据比较大，假如说数据是百万级别，顶点的数据就有好几万甚至更多，下面的语句
select * from t where pid is null
union all
select a.* from t a inner join vs b on a.pid = b.id where b.value < a.value and b.pid is null
freetd 2019-03-01
- 打赏
- 举报
后面那个意思差不多了，还有少少需要改进，可以结帖
二月十六 2019-03-01
- 打赏
- 举报
;with t as (
select 1 as id, null as pid, value = 1.1
union all
select 2 as id, 1 as pid, value = 1.3
union all
select 2 as id, 1 as pid, value = 1.0
union all
select 2 as id, 1 as pid, value = 2.1
union all
select 2 as id, 1 as pid, value = 2.9
union all
select 2 as id, 1 as pid, value = 0.9
union all
select 7 as id, 2 as pid, value = 2.1
union all
select 7 as id, 2 as pid, value = 2.9
union all
select 7 as id, 2 as pid, value = 0.9
union all
select 3 as id, null as pid, value = 2.1
union all
select 5 as id, 3 as pid, value = 3.3
union all
select 5 as id, 3 as pid, value = 1.0
union all
select 5 as id, 3 as pid, value = 9.1
union all
select 5 as id, 3 as pid, value = 2.9
union all
select 5 as id, 3 as pid, value = 0.9
)
,vs as (
select * from t where pid is null
union all
select a.* from t a inner join vs b on a.pid = b.id where b.value < a.value and b.pid is null
)
SELECT * FROM (
SELECT *,ROW_NUMBER()OVER(PARTITION BY pid ORDER BY value) rn FROM vs
)t1 WHERE pid IS NULL OR rn=1
二月十六 2019-03-01
- 打赏
- 举报
;with t as (
select 1 as id, null as pid, value = 1.1
union all
select 2 as id, 1 as pid, value = 1.3
union all
select 2 as id, 1 as pid, value = 1.0
union all
select 2 as id, 1 as pid, value = 2.1
union all
select 2 as id, 1 as pid, value = 2.9
union all
select 2 as id, 1 as pid, value = 0.9
union all
select 7 as id, 2 as pid, value = 2.1
union all
select 7 as id, 2 as pid, value = 2.9
union all
select 7 as id, 2 as pid, value = 0.9
union all
select 3 as id, null as pid, value = 2.1
union all
select 5 as id, 3 as pid, value = 3.3
union all
select 5 as id, 3 as pid, value = 1.0
union all
select 5 as id, 3 as pid, value = 9.1
union all
select 5 as id, 3 as pid, value = 2.9
union all
select 5 as id, 3 as pid, value = 0.9
),t1 AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY pid ORDER BY t.value DESC) rn FROM t
)
,vs as (
select * from t1 where pid is null
union all
select a.* from t1 a inner join vs b on a.pid = b.id where b.value < a.value and b.pid is NULL
)select * from vs WHERE vs.rn=1 OR pid IS NULL
freetd 2019-02-28
- 打赏
- 举报
图片补充
