- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有 [Friends] 节点和 [Link] 边缘。我的任务是找出哪个子图(网络)属于哪个 friend 。对此的简单说明:
这是我如何构造节点、边和填充数据的代码:
-- Construct nodes
DROP TABLE IF EXISTS [dbo].[Friend];
CREATE TABLE [dbo].[Friend] (
[FriendId] INT IDENTITY PRIMARY KEY
, [Name] NVARCHAR(256) NOT NULL
) AS NODE;
INSERT INTO [dbo].[Friend]([Name]) VALUES
('Jon'), ('Rita'), ('Ben'), ('Andrew'), ('Joe'), ('Patrick')
, ('Mike'), ('Thomas'), ('Kelly'), ('Lily'), ('Kira');
-- Construct edges
DROP TABLE IF EXISTS [dbo].[Link];
CREATE TABLE [dbo].[Link] (
[LinkDirection] BIT NOT NULL
, [FriendIdFrom] INT NOT NULL
, [FriendIdTo] INT NOT NULL
)AS EDGE;
INSERT INTO [dbo].[Link] ($from_id, $to_id, [LinkDirection], [FriendIdFrom], [FriendIdTo])
-- Network 1
SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Jon' AND [f2].[Name] = 'Rita'
UNION ALL SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Jon' AND [f2].[Name] = 'Ben'
UNION ALL SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Jon' AND [f2].[Name] = 'Andrew'
UNION ALL SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Andrew' AND [f2].[Name] = 'Joe'
UNION ALL SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Andrew' AND [f2].[Name] = 'Patrick'
-- Network 2
UNION ALL SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Mike' AND [f2].[Name] = 'Thomas'
UNION ALL SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Mike' AND [f2].[Name] = 'Kelly'
-- Network 3
UNION ALL SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Lily' AND [f2].[Name] = 'Kira'
-- To have bi-directional link
-- Network 1
UNION ALL SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Jon' AND [f2].[Name] = 'Rita'
UNION ALL SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Jon' AND [f2].[Name] = 'Ben'
UNION ALL SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Jon' AND [f2].[Name] = 'Andrew'
UNION ALL SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Andrew' AND [f2].[Name] = 'Joe'
UNION ALL SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Andrew' AND [f2].[Name] = 'Patrick'
-- Network 2
UNION ALL SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Mike' AND [f2].[Name] = 'Thomas'
UNION ALL SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Mike' AND [f2].[Name] = 'Kelly'
-- Network 3
UNION ALL SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Lily' AND [f2].[Name] = 'Kira';
我有代码,如何列出所有可能的路径:
SELECT
[StartNode] = [f1].[Name]
, [FinalNode] = LAST_VALUE([f2].[name]) WITHIN GROUP (GRAPH PATH)
, [Steps] = COUNT([f2].[FriendId]) WITHIN GROUP (GRAPH PATH)
, [Path] = [f1].[Name] + ' => ' + STRING_AGG([f2].[Name],' => ') WITHIN GROUP (GRAPH PATH)
, [Network] = '???'
--, *
FROM
[dbo].[Friend] [f1]
, [dbo].[Friend] FOR PATH [f2]
, [dbo].[Link] FOR PATH [l]
WHERE
MATCH(SHORTEST_PATH(f1(-(l)->f2)+));
但是,我很难在这里添加网络标识符(唯一数字,文本,无关紧要):(
---------------------------------------------------------------------------------
|StartNode | FinalNode | Steps | Path |Network|
|------------| -----------| -------| -----------------------------------|-------|
|Jon | Rita | 1 | Jon => Rita |1 |
|Jon | Ben | 1 | Jon => Ben |1 |
|Jon | Andrew | 1 | Jon => Andrew |1 |
|Rita | Jon | 1 | Rita => Jon |1 |
|Ben | Jon | 1 | Ben => Jon |1 |
|Andrew | Jon | 1 | Andrew => Jon |1 |
|Andrew | Joe | 1 | Andrew => Joe |1 |
|Andrew | Patrick | 1 | Andrew => Patrick |1 |
|Joe | Andrew | 1 | Joe => Andrew |1 |
|Patrick | Andrew | 1 | Patrick => Andrew |1 |
|Mike | Thomas | 1 | Mike => Thomas |2 |
|Mike | Kelly | 1 | Mike => Kelly |2 |
|Thomas | Mike | 1 | Thomas => Mike |2 |
|Kelly | Mike | 1 | Kelly => Mike |2 |
|Lily | Kira | 1 | Lily => Kira |3 |
|Kira | Lily | 1 | Kira => Lily |3 |
|Jon | Jon | 2 | Jon => Andrew => Jon |1 |
|Jon | Joe | 2 | Jon => Andrew => Joe |1 |
|Jon | Patrick | 2 | Jon => Andrew => Patrick |1 |
|Rita | Rita | 2 | Rita => Jon => Rita |1 |
|Rita | Ben | 2 | Rita => Jon => Ben |1 |
|Rita | Andrew | 2 | Rita => Jon => Andrew |1 |
|Ben | Rita | 2 | Ben => Jon => Rita |1 |
|Ben | Ben | 2 | Ben => Jon => Ben |1 |
|Ben | Andrew | 2 | Ben => Jon => Andrew |1 |
|Andrew | Rita | 2 | Andrew => Jon => Rita |1 |
|Andrew | Ben | 2 | Andrew => Jon => Ben |1 |
|Andrew | Andrew | 2 | Andrew => Jon => Andrew |1 |
|Joe | Jon | 2 | Joe => Andrew => Jon |1 |
|Joe | Joe | 2 | Joe => Andrew => Joe |1 |
|Joe | Patrick | 2 | Joe => Andrew => Patrick |1 |
|Patrick | Jon | 2 | Patrick => Andrew => Jon |1 |
|Patrick | Joe | 2 | Patrick => Andrew => Joe |1 |
|Patrick | Patrick | 2 | Patrick => Andrew => Patrick |1 |
|Mike | Mike | 2 | Mike => Thomas => Mike |2 |
|Thomas | Thomas | 2 | Thomas => Mike => Thomas |2 |
|Thomas | Kelly | 2 | Thomas => Mike => Kelly |2 |
|Kelly | Thomas | 2 | Kelly => Mike => Thomas |2 |
|Kelly | Kelly | 2 | Kelly => Mike => Kelly |2 |
|Lily | Lily | 2 | Lily => Kira => Lily |3 |
|Kira | Kira | 2 | Kira => Lily => Kira |3 |
|Rita | Joe | 3 | Rita => Jon => Andrew => Joe |1 |
|Rita | Patrick | 3 | Rita => Jon => Andrew => Patrick |1 |
|Ben | Joe | 3 | Ben => Jon => Andrew => Joe |1 |
|Ben | Patrick | 3 | Ben => Jon => Andrew => Patrick |1 |
|Joe | Rita | 3 | Joe => Andrew => Jon => Rita |1 |
|Joe | Ben | 3 | Joe => Andrew => Jon => Ben |1 |
|Patrick | Rita | 3 | Patrick => Andrew => Jon => Rita |1 |
|Patrick | Ben | 3 | Patrick => Andrew => Jon => Ben |1 |
---------------------------------------------------------------------------------
简而言之,我需要如何为这些网络赋予唯一名称,并与人进行映射。
最佳答案
select *,
min([FinalNode]) over(partition by [StartNode]) as NetworkOf,
----in case of duplicate names, also use partition by StartNodeId instead of [StartNode]...
min(FinalFriendId) over(partition by [StartNode]) as NetworkId
from
(
SELECT
[StartNode] = [f1].[Name]
, [FinalNode] = LAST_VALUE([f2].[name]) WITHIN GROUP (GRAPH PATH)
, [Steps] = COUNT([f2].[FriendId]) WITHIN GROUP (GRAPH PATH)
, [Path] = [f1].[Name] + ' => ' + STRING_AGG([f2].[Name],' => ') WITHIN GROUP (GRAPH PATH)
, FinalFriendId = LAST_VALUE(f2.FriendId) WITHIN GROUP (GRAPH PATH)
FROM
[dbo].[Friend] [f1]
, [dbo].[Friend] FOR PATH [f2]
, [dbo].[Link] FOR PATH [l]
WHERE
MATCH(SHORTEST_PATH(f1(-(l)->f2)+))
) as src;
关于sql-server - SQL Server 图 : how identify subgraphs?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/63767196/
更新:添加关于 Hashable 的相同错误 我已经创建了一个 Identifiable 兼容协议(protocol)和兼容结构。然后,当我创建列表并在 ForEach 中引用它时,我收到错误 Typ
这只是我偶然发现的例子! 我正在使用 cout与 operator // imports the declaration of std::cout using namespace std; // ma
我有一些表,我使用 MySQL Workbench 创建了 role_has_action 表。 创建的字段是:(role_id,action_id,action_controller_id): (为
我有一个 codesign 无法完全验证的应用程序,因为它“不满足其指定的要求”。第一次检查返回“在磁盘上有效”,所以没关系。 codesign -dvvvv -r- PATH_TO_APP 告诉我要
我正在使用 Java SDK 创建 SAS 来访问 blob。这是代码: SharedAccessBlobPolicy policy = new SharedAccessBlobPolicy(); p
#include "stdafx.h" #include #include #include #include #include using namespace std; #define T
我在代码中看到了这两种方法。你能解释一下这两者有什么区别吗?正如我认为它与 C++ 完成命名空间查找的方式有关,您能否也提供一些相关信息,或者提供一个好的文档的链接?谢谢。 最佳答案 示例: #inc
我一直在使用一个工具 sbconstants从我的 Xcode 项目中的 Storyboard 标识符和重用标识符创建外部常量。 我已将包含这些常量的所有声明的 header #imported 到项
我想知道 bundle Identifier(在 info.plist 中)之间的区别。以及产品 Bundle Identifier(在 Build Setting -> Packaging -> P
我有课Identifier它本质上是 UUID 的类型安全包装器(因此类 Foo 包含 Identifier )。 FooStore类有一个方法 List> bulkReadIdentifiers()
在 Go 中,公共(public)名称以大写字母开头,私有(private)名称以小写字母开头。 我正在编写一个不是库的程序,它是一个单独的包。是否有任何 Go 习语规定我的标识符应该全部公开还是全部
我有一个页面 url,它看起来像: http://mydomain.com/nodes/32/article/new?return=view 安装 tomcat 7 后,尝试访问它时出现此异常: /n
我正在学习以下教程: http://www.appcoda.com/ios7-programming-ibeacons-tutorial/ 但是,我没有使用 iPhone 作为信标,而是使用制造商(R
我在为我的 iPhone 应用程序的下一版本上传 .app 文件时收到此错误“Bundle Identifier differents from prior bundle identifier”。 注
Scene 1, Layer 'script', Frame 1, Line 9 1084: Syntax error: expecting identifier before this. Sc
升级到 Xcode 7 后,我注意到 CFBundleIdentifier 已开始指向在 Build Settings/Packaging 中找到的产品捆绑标识符,而不是 Info.Plist 中的捆
关闭。这个问题需要debugging details .它目前不接受答案。 想改进这个问题?将问题更新为 on-topic对于堆栈溢出。 5年前关闭。 Improve this question 我在
我使用 Apache DBCP 来获取连接池,我每次都使用 PoolingDataSource 来获取连接。当我向数据库中插入一个对象时,它工作得很好,但是当我尝试从数据库中选择一个元素时,就会出现问
由于我项目的 react-native 版本 (0.44.3),我正在尝试在版本 0.6.4 中安装包 react-native-today-widget,我能够成功安装包: yarn add rea
之前有人问过这个问题,我已经查看了所有其他 stackoverflow 主题的答案,但我无法解决这个问题。 我的应用程序在所有平台的模拟器中运行良好,但是当我在我的设备上运行该应用程序时,我收到错误代
我是一名优秀的程序员,十分优秀!