请教一个SQL查询算法, 请大神帮忙

2018-08-17 07:55:04 1.4k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

HOPE：基于强化学习的混合策略路径规划器，适用于多样化停车场景本文提出了一种混合策略路径规划器（HOPE），用于解决自动停车中的路径规划问题。HOPE结合了强化学习（RL）和Reeds-Shepp（RS）曲线，通过动作掩码机制引导RL智能体的探索，并利用Transformer融合环境感知信息与掩码。该方法在多样且复杂的停车场景中实现了超过97%的成功率，并通过真实实验验证了其泛化能力。此外，本文还提出了基于空间与障碍物分布的停车场景难度分级标准，进一步提升了训练与评估的效率。实验结果表明，HOPE在规划成功率和跨场景泛化能力上均优于传统的基于规则算法和朴素强化学习方法阅读详情

我有如下的示例数据
字段1 字段2
A A
A B
B C
B D
E F
G H
G F
J D
K C

我想遍历上述数据, 并将内容分组, 结果如下
比如, 从上表我们可以的得到A关联了AB, B又关联了CD, CD又分别关联了JK. 所以得出ABCDJK为一组.
最终结果如下
组号分组内容
1 A
1 B
1 C
1 D
1 J
1 K
2 E
2 F
2 G
2 H

...全文

如何用Lmcache+vllm优化KVcache存储？CPU与SSD性能实测对比本文通过实测对比，详细介绍了如何利用Lmcache+vLLM将大模型推理中的KV Cache卸载至CPU内存或SSD硬盘，以解决长上下文场景下的GPU显存瓶颈。测试数据显示，CPU卸载方案在首字延迟（TTFT）上显著优于SSD方案，但SSD方案在存储成本上更具优势。文章提供了完整的配置指南、性能数据分析和生产环境选型建议。阅读详情

icelovey

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

楊佳易 2019-01-04

打赏
举报

顶一下，有没有高手来解决？？

砸死牛顿的苹果 2018-12-27

打赏
举报

有点像无向图的结点集合

楊佳易 2018-12-27

打赏
举报

我在RELATION表里建了ColA和ColB的列，里面的值都是一样的。别介意。

楊佳易 2018-12-27

打赏
举报

你好。谢回复。首先，在


select 'A' as c1,'A'  as c2 union all 
select 'A' as c1,'B'  as c2 union all 
select 'B' as c1,'C'  as c2 union all 
select 'B' as c1,'D'  as c2 union all 
select 'E' as c1,'F'  as c2 union all 
select 'G' as c1,'H'  as c2 union all 
select 'G' as c1,'F'  as c2 union all 
select 'J' as c1,'D'  as c2 union all 
select 'K' as c1,'C'  as c2

这个关系表里的最后添加A和G的关系。如下，


select 'A' as c1,'A'  as c2 union all 
select 'A' as c1,'B'  as c2 union all 
select 'B' as c1,'C'  as c2 union all 
select 'B' as c1,'D'  as c2 union all 
select 'E' as c1,'F'  as c2 union all 
select 'G' as c1,'H'  as c2 union all 
select 'G' as c1,'F'  as c2 union all 
select 'J' as c1,'D'  as c2 union all 
select 'K' as c1,'C'  as c2 union all
select 'A' as c1,'G'  as c2

然后你可分析一下C1和C2之间的关系。 A A →A和A，是本身。 A B →A和B，说明，AB有关系。 B C →B和C，说明，ABC有关系。 B D →B和D，说明，ABCD有关系。 E F →E和F，说明，EF有关系。 G H →G和H，说明，GH有关系。 G F →G和F，说明，EFGH有关系。 J D →J和D，说明，ABCDJ有关系。 K C →K和C，说明，ABCDJK有关系。所以，分为2组。到这里，应该认识都一样把。那么，在最后一条，加上A和G，换句话说，ABCDEFGHJK都有关系。只有1组。而，以上的SQL语句，只针对原来数据进行组合加工，没有考虑到在之后添加条件，这样，这个SQL就是BUG。并不是从根据答案去写SQL，而应该是先要把他们的关系整理好之后，再写。这是我认为的。我看，这样的问题，不写循环游标，可能无法实现。我自己也写了一段，可以正对最后的A，G关系。但是，我把A，G关系删掉之后，会有BUG，Oh，MyGod。


with main as (
select * from 
(select ColA,ColB from RELATION
union
select ColB,ColA from RELATION) as A
)
,sub as (
select ROW_NUMBER() over(order by sort,ColA) no ,* from main 
Cross Apply(
select min(ColB) sort from main as sub
where main.ColA = sub.ColA
group by ColA
) as B
)
, sub2 as (
select sub.no,sub.ColA,sub.ColB,isnull(B.sort,0)  as chk
from sub
outer apply(
select * from sub as sub3
where sub.no > sub3.no
and(
sub.ColA = sub3.ColB
or sub.ColB = sub3.ColB
or sub.ColA  = sub3.ColB
)
) as B)


select distinct 
(select count(*) from sub2 as s
where s.chk = '0'
and sub2.no >= s.no)grpno,
ColA from sub2
order by grpno,ColA
;

你可以参考一下。看看。这个难题，如果不使用循环去解决？期待你的回复。谢谢。

引用 8 楼 Dear SQL 的回复:

怎么加A和G?发出来看下 [quote=引用 5 楼 weixin_44238511 的回复:] 如果在最后一条记录上加上A和G的话，你这个SQL会分成2组，有Bug。 [quote=引用 4 楼 Dear SQL 的回复:]

with tbl1 as 
(
select 'A' as c1,'A'  as c2 union all 
select 'A' as c1,'B'  as c2 union all 
select 'B' as c1,'C'  as c2 union all 
select 'B' as c1,'D'  as c2 union all 
select 'E' as c1,'F'  as c2 union all 
select 'G' as c1,'H'  as c2 union all 
select 'G' as c1,'F'  as c2 union all 
select 'J' as c1,'D'  as c2 union all 
select 'K' as c1,'C'  as c2
),tab2 as(
	select * from tbl1 where c1<=c2
	union all
	select c2,c1 from tbl1 where c1>c2
),list as(
	select groupcode=ROW_NUMBER()over(order by c1),c1,c2
	from tab2
	where c1 not in(select c2 from tab2) or c1=c2
	union all
	select b.groupcode,a.c1,a.c2
	from tab2 a
	inner join list b on b.c2=a.c1 and a.c1<>a.c2
)
select groupcode,c1
from list
union 
select groupcode,c2
from list

groupcode            c1
-------------------- ----
1                    A
1                    B
1                    C
1                    D
1                    J
1                    K
2                    E
2                    F
2                    G
2                    H

[/quote][/quote]

楊佳易 2018-12-27

打赏
举报

有哪一位大神来接解决？如果使用Graph Database，Edge和Node的关系，使用WHERE MATCH语句，可以找到关系。用PowerBI来表示就可。但是要使用T-SQL的一般Query的话，还是难。谁能不使用循环来接写这个T-SQL？等高手。

楊佳易 2018-12-24

打赏
举报

引用 3 楼 weixin_41779699 的回复:

with tbl1 as
(
select 'A' as c1,'A' as c2 union all
select 'A' as c1,'B' as c2 union all
select 'B' as c1,'C' as c2 union all
select 'B' as c1,'D' as c2 union all
select 'E' as c1,'F' as c2 union all
select 'G' as c1,'H' as c2 union all
select 'G' as c1,'F' as c2 union all
select 'J' as c1,'D' as c2 union all
select 'K' as c1,'C' as c2
),
tbl2
as
(select ROW_NUMBER() over (order by case when c1<c2 then c1 else c2 end ) rid, * from tbl1 where c1 <> c2) --Add unique identity
,tbl3
as
(select rid,c1 from tbl2
union all
select rid,c2 from tbl2)
,tbl
as
(
select ROW_NUMBER() over (order by c1) bid,*
from (select a.c1 ,min(a.rid) rid from tbl3 a group by a.c1 having count(a.rid) = 1) a
where exists(select 1 from tbl3 where a.rid = rid and a.c1 < c1)
union all
select b.bid, a.c1,a.rid from tbl3 a cross apply (select bid from tbl where a.c1 = c1 and a.rid > rid or a.c1 > c1 and a.rid = rid) b
)
select distinct bid,c1 from tbl
order by bid,c1

你这个SQL也是，如果在最后加上A和G，那么，所有的关系就是1组了。有Bug。

楊佳易 2018-12-24

打赏
举报

如果在最后一条记录上加上A和G的话，你这个SQL会分成2组，有Bug。

引用 4 楼 Dear SQL 的回复:

with tbl1 as 

(

select 'A' as c1,'A'  as c2 union all 

select 'A' as c1,'B'  as c2 union all 

select 'B' as c1,'C'  as c2 union all 

select 'B' as c1,'D'  as c2 union all 

select 'E' as c1,'F'  as c2 union all 

select 'G' as c1,'H'  as c2 union all 

select 'G' as c1,'F'  as c2 union all 

select 'J' as c1,'D'  as c2 union all 

select 'K' as c1,'C'  as c2

),tab2 as(

	select * from tbl1 where c1<=c2

	union all

	select c2,c1 from tbl1 where c1>c2

),list as(

	select groupcode=ROW_NUMBER()over(order by c1),c1,c2

	from tab2

	where c1 not in(select c2 from tab2) or c1=c2

	union all

	select b.groupcode,a.c1,a.c2

	from tab2 a

	inner join list b on b.c2=a.c1 and a.c1<>a.c2

)

select groupcode,c1

from list

union 

select groupcode,c2

from list



groupcode            c1

-------------------- ----

1                    A

1                    B

1                    C

1                    D

1                    J

1                    K

2                    E

2                    F

2                    G

2                    H

Dear SQL(燊) 2018-12-24

打赏
举报

怎么加A和G?发出来看下

引用 5 楼 weixin_44238511 的回复:

如果在最后一条记录上加上A和G的话，你这个SQL会分成2组，有Bug。 [quote=引用 4 楼 Dear SQL 的回复:]

with tbl1 as 
(
select 'A' as c1,'A'  as c2 union all 
select 'A' as c1,'B'  as c2 union all 
select 'B' as c1,'C'  as c2 union all 
select 'B' as c1,'D'  as c2 union all 
select 'E' as c1,'F'  as c2 union all 
select 'G' as c1,'H'  as c2 union all 
select 'G' as c1,'F'  as c2 union all 
select 'J' as c1,'D'  as c2 union all 
select 'K' as c1,'C'  as c2
),tab2 as(
	select * from tbl1 where c1<=c2
	union all
	select c2,c1 from tbl1 where c1>c2
),list as(
	select groupcode=ROW_NUMBER()over(order by c1),c1,c2
	from tab2
	where c1 not in(select c2 from tab2) or c1=c2
	union all
	select b.groupcode,a.c1,a.c2
	from tab2 a
	inner join list b on b.c2=a.c1 and a.c1<>a.c2
)
select groupcode,c1
from list
union 
select groupcode,c2
from list

groupcode            c1
-------------------- ----
1                    A
1                    B
1                    C
1                    D
1                    J
1                    K
2                    E
2                    F
2                    G
2                    H

[/quote]

楊佳易 2018-12-24

打赏
举报

请问这个帖子，谁能写正确的答案呢？我也想了好几次，使用CROSS JOIN和OUTER APPLY，应该可以解决吧。期待高手出现。

Dear SQL(燊) 2018-12-10

打赏
举报

with tbl1 as 
(
select 'A' as c1,'A'  as c2 union all 
select 'A' as c1,'B'  as c2 union all 
select 'B' as c1,'C'  as c2 union all 
select 'B' as c1,'D'  as c2 union all 
select 'E' as c1,'F'  as c2 union all 
select 'G' as c1,'H'  as c2 union all 
select 'G' as c1,'F'  as c2 union all 
select 'J' as c1,'D'  as c2 union all 
select 'K' as c1,'C'  as c2
),tab2 as(
	select * from tbl1 where c1<=c2
	union all
	select c2,c1 from tbl1 where c1>c2
),list as(
	select groupcode=ROW_NUMBER()over(order by c1),c1,c2
	from tab2
	where c1 not in(select c2 from tab2) or c1=c2
	union all
	select b.groupcode,a.c1,a.c2
	from tab2 a
	inner join list b on b.c2=a.c1 and a.c1<>a.c2
)
select groupcode,c1
from list
union 
select groupcode,c2
from list

groupcode            c1
-------------------- ----
1                    A
1                    B
1                    C
1                    D
1                    J
1                    K
2                    E
2                    F
2                    G
2                    H

二月十六 2018-08-18

打赏
举报

这个关联从1到2算，从2到1也算？

卖水果的net 2018-08-18

打赏
举报

1、会不会出现循环引用的，如果存在，怎么一个显示规则？ 2、有没有标识出哪个是第一条数据。

weixin_41779699 2018-08-18

打赏
举报

with tbl1 as
(
select 'A' as c1,'A' as c2 union all
select 'A' as c1,'B' as c2 union all
select 'B' as c1,'C' as c2 union all
select 'B' as c1,'D' as c2 union all
select 'E' as c1,'F' as c2 union all
select 'G' as c1,'H' as c2 union all
select 'G' as c1,'F' as c2 union all
select 'J' as c1,'D' as c2 union all
select 'K' as c1,'C' as c2
),
tbl2
as
(select ROW_NUMBER() over (order by case when c1<c2 then c1 else c2 end ) rid, * from tbl1 where c1 <> c2) --Add unique identity
,tbl3
as
(select rid,c1 from tbl2
union all
select rid,c2 from tbl2)
,tbl
as
(
select ROW_NUMBER() over (order by c1) bid,*
from (select a.c1 ,min(a.rid) rid from tbl3 a group by a.c1 having count(a.rid) = 1) a
where exists(select 1 from tbl3 where a.rid = rid and a.c1 < c1)
union all
select b.bid, a.c1,a.rid from tbl3 a cross apply (select bid from tbl where a.c1 = c1 and a.rid > rid or a.c1 > c1 and a.rid = rid) b
)
select distinct bid,c1 from tbl
order by bid,c1

请教一个SQL查询算法, 请大神帮忙

相关推荐