SQL Server中的查询非常困难 [英] A query in SQL server very hard

问题描述

大家好！
我在SQL Server中有问题必须所有人的帮助.
我的数据库的格式为(ProID，SubProid，值)
例如:

A---B---0.2
B---C---0.1
C---D---0.5
A---D---0.6
B---D---0.3

给定的SubproId可以具有从给定proID到达的多个路径.
如何从给定的2个点计算每个路径的总权重
示例(A-D = 0.6 + 0.8 + 0.5 = 1.9之间的每个权重之和)?

解决方案

您应研究以下内容

最短路径问题/算法

问题的呈现向我暗示这是家庭作业.解决起来并不简单，并且需要对算法和SQL有一定的了解才能自行解决(我当然不能在不研究算法方面的情况下解决它.)

<我认为这不是最短路径"问题.我们可以称之为所有路径"的更多信息-但我不确定.好的，T-SQL不是图算法的语言，但也不是不可能.
这是一篇很好的文章，介绍了t-sql中一些与图形有关的一般问题: http://hansolav.net/sql/graphs. html [ ^ ].这是有关所有路径"问题的文章: ^ ].这不是t-sql，但是您可以理解这个概念.
您可以将这些方法用作示例和起点.如您所见，这不是最简单的问题.我敢肯定，您将需要使用一些临时表和游标之类的东西.

Hi all!
I have a problem in SQL Server must everyone help.
My database has the form (ProID, SubProid, value)
example:

A---B---0.2
B---C---0.1
C---D---0.5
A---D---0.6
B---D---0.3

a given SubproId can have multiple paths to reach from a given proID.
How do I calculate the sum weight of every the path from the 2 points given
example(sum of every weight between A--D=0.6+0.8+0.5=1.9)?

解决方案

You should research the following

shortest path problem/algorithm

The presentation of the problem suggests to me it is homework. It isn''t simple to solve and requires a non-trivial understanding of algorithms and SQL to solve it on ones own (I certainly couldn''t solve it without researching the algorithm side.)

I don''t see this as "shortest path" problem. More something that we could call "all paths" - but I am not sure. Ok, T-SQL is not the language for graph algorithms, but not impossible to handle either.
This is a good article about some general graph-related problem solutions in t-sql: http://hansolav.net/sql/graphs.html[^]. Here is an article about the "all paths" problem: http://www.technical-recipes.com/2011/a-recursive-algorithm-to-find-all-paths-between-two-given-nodes/[^]. This is not t-sql, but you can understand the concept.
You could use these approaches as samples and starting point. As you can see, it is not the simplest problem. I am pretty sure you will need to use fancy things like temporary tables and cursors.

这篇关于SQL Server中的查询非常困难的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！