带分割功能的Sql存储过程 [英] Sql Stored procedure with split function

问题描述

请帮我解决这个问题....



表架构< b> Ops_Route_Master

Hi,

Please help me in this issue....

Table Schema Ops_Route_Master

RouteId      (Primary key) (varchar(15))
RouteSummary               (varchar(MAX))

样本数据：

Sample data:

RouteId         RouteSummary
-------------------------------------------------
R1              A-B-C-D-A
R2              C-P-Q-C
-------------------------------------------------

这里A，B，C ......是位置，例如A表示Dealer_Hyderabad



需要从给定的输入字符串中找出组合的程序。



例如，最初我们选择路线R1

但是到达之后位置C，车辆被重定向到两次覆盖路线R2。



如果输入字符串 ABCPQCPQCDA ，则需要输出为

RouteId --> count
R1      -->     1
R2      -->     2

问题是他们不直接选择路线..

说，在R2中旅行。

他们只会说从C转到P，然后从P转到Q等等......



因此我们将在到达之后获得最后一个字符串车辆已经开始了它的旅程。

Problem is they dont select route directly..
Say, Travel in R2 like that.
They will only say go to P from C, then go to Q from P and so on...

So we will get a final string after reaching the point where the vehicle has started its journey.

推荐答案

由于很多原因，没有简单的方法可以达到这个目的，例如：

1 ）路线摘要存储为文本

2）路线点重复（例如C）。



见下面的例子（阅读评论）

There is no simple way to achieve that, because of lot of reasons, for example:
1) route summary is stored as a text
2) route points are duplicated (for example C).

See below example (read comments)

--declare master table
DECLARE @Ops_Route_Master TABLE (RouteId varchar(15), RouteSummary varchar(MAX))
--insert data
INSERT INTO @Ops_Route_Master (RouteId, RouteSummary)
VALUES('R1','A-B-C-D-A')
INSERT INTO @Ops_Route_Master (RouteId, RouteSummary)
VALUES('R2','C-P-Q-C')

--declare temporary table to get data in format:
--R1 | A
--R1 | B
--R1 | C
DECLARE @RoutedPoints TABLE(RouteId varchar(15), PointNo INT, RoutePoint varchar(15))

--add data
;WITH RoutePoints AS
(
	SELECT RouteId, LEFT(RouteSummary, CHARINDEX('-',RouteSummary)-1) AS RoutePoint, RIGHT(RouteSummary, LEN(RouteSummary) - CHARINDEX('-',RouteSummary)) AS Remainder
	FROM @Ops_Route_Master
	WHERE CHARINDEX('-',RouteSummary)>0
	UNION ALL
	SELECT RouteId, LEFT(Remainder, CHARINDEX('-',Remainder)-1) AS RoutePoint, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('-',Remainder)) AS Remainder
	FROM RoutePoints
	WHERE CHARINDEX('-',Remainder)>0
	UNION ALL
	SELECT RouteId, Remainder AS RoutePoint, NULL AS Remainder
	FROM RoutePoints
	WHERE CHARINDEX('-',Remainder)=0
)
INSERT INTO @RoutedPoints (RouteId, PointNo, RoutePoint)
SELECT RouteId, ROW_NUMBER() OVER(PARTITION BY RouteId ORDER BY RouteId) AS PointNo, RoutePoint
FROM RoutePoints

--uncomment it to check results
--SELECT *
--FROM @RoutedPoints

--declare table to store route to find
DECLARE @Route2Find TABLE (RoutePoints varchar(MAX))

--insert data
INSERT INTO @Route2Find (RoutePoints)
VALUES('A-B-C-P-Q-C-P-Q-C-D-A')

--declare table to store data in format:
-- 1 | A
-- 2 | B
-- ...
-- 11| A
DECLARE @FindPoints TABLE (Id INT, Point varchar(MAX))

--add data
;WITH SearchedPoints AS
(
	SELECT 1 AS Id, LEFT(RoutePoints, CHARINDEX('-',RoutePoints)-1) AS Point, RIGHT(RoutePoints, LEN(RoutePoints) - CHARINDEX('-',RoutePoints)) AS Remainder
	FROM @Route2Find
	WHERE CHARINDEX('-',RoutePoints)>0
	UNION ALL
	SELECT Id + 1 AS Id, LEFT(Remainder, CHARINDEX('-',Remainder)-1) AS Point, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('-',Remainder)) AS Remainder
	FROM SearchedPoints
	WHERE CHARINDEX('-',Remainder)>0
	UNION ALL
	SELECT Id + 1 AS Id, Remainder AS RoutePoint, NULL AS Remainder
	FROM SearchedPoints
	WHERE CHARINDEX('-',Remainder)=0
)
INSERT INTO @FindPoints (Id, Point)
SELECT Id, Point
FROM SearchedPoints

--uncomment it to check results
--SELECT *
--FROM @RoutedPoints

--uncomment it to check results
--SELECT *
--FROM @FindPoints

--global
SELECT T.RouteId, COUNT(T.RouteId) AS CountOfPoints 
FROM (
	SELECT DISTINCT t2.RouteId, t1.Point
	FROM @FindPoints AS t1 INNER JOIN @RoutedPoints AS t2 ON t1.Point =t2.RoutePoint
	) AS T
GROUP BY T.RouteId

查询标记为： - 全球返回：

Query marked as: --global returns:

R1  4
R2  3

如果你想看看为什么会这样，运行这个查询：

If you want to see why it happens, run this query:

SELECT DISTINCT src.Id, src.Point AS FromPoint, dst.Point AS ToPoint, rp.RouteId
FROM @FindPoints AS src
  INNER JOIN  @FindPoints AS dst ON src.Id+1 = dst.Id
  LEFT JOIN  @RoutedPoints AS rp ON src.Point = rp.RoutePoint

这篇关于带分割功能的Sql存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！