作为一个例子我有5个对象。 一个目的是结合在一起或彼此相邻的红点。 换句话说X + 1或X-1或Y + 1或Y-1。
我需要创建一个包含每个对象像的第一XY坐标MS SQL 视图 ：
X,Y
=======
1. 1,1
2. 1,8
3. 4,3
4. 5,7
5. 6,5
我无法弄清楚如何将其在一个视图组（不使用存储过程）。 任何人有任何的想法是有很大的帮助。 谢谢
Answer 1:
对方的回答是已经相当长，所以我要离开它就好了。 这个答案是更好，更简单，也是正确的，而其他人有一些边缘的情况下，这将产生一个错误的答案 - 我留待读者做练习。
注：换行被添加为清楚起见。 整个块是单个查询
;with Walker(StartX,StartY,X,Y,Visited) as (
select X,Y,X,Y,CAST('('+right(X,3)+','+right(Y,3)+')' as Varchar(Max))
from puzzle
union all
select W.StartX,W.StartY,P.X,P.Y,W.Visited+'('+right(P.X,3)+','+right(P.Y,3)+')'
from Walker W
join Puzzle P on
(W.X=P.X and W.Y=P.Y+1 OR -- these four lines "collect" a cell next to
W.X=P.X and W.Y=P.Y-1 OR -- the current one in any direction
W.X=P.X+1 and W.Y=P.Y OR
W.X=P.X-1 and W.Y=P.Y)
AND W.Visited NOT LIKE '%('+right(P.X,3)+','+right(P.Y,3)+')%'
)
select X, Y, Visited
from
(
select W.X, W.Y, W.Visited, rn=row_number() over (
partition by W.X,W.Y
order by len(W.Visited) desc)
from Walker W
left join Walker Other
on Other.StartX=W.StartX and Other.StartY=W.StartY
and (Other.Y<W.Y or (Other.Y=W.Y and Other.X<W.X))
where Other.X is null
) Z
where rn=1
第一步是建立一个“行者”递归表表达式将开始在每一个细胞，并尽可能它可以旅行，不必追溯的任何步骤。 确保电池不会重新审议通过使用访问列，它存储已经从每一个起点访问的每个细胞进行。 尤其是，这种情况AND W.Visited NOT LIKE '%('+right(PX,3)+','+right(PY,3)+')%'拒绝它已经访问过的小区。
要了解如何其余的工作，你需要看一下运行的“行者” CTE所产生的结果，CTE后“的运行startx，StartY选择从沃克顺序*”。 A“片”，用5个细胞出现在至少5个组，每个组具有不同的(StartX,StartY)但各组具有的所有5 (X,Y)个具有不同“访问”路径。
子查询（Z）使用LEFT JOIN + IS NULL剔除组向下到包含“第一XY坐标”各组中的单列，由条件限定
Other.StartX=W.StartX and Other.StartY=W.StartY
and (Other.Y<W.Y or (Other.Y=W.Y and Other.X<W.X))
意图是，可以从（STARTX，StartY）开始来访问的每个单元，以比较在相同的组中的每个其他细胞，从而找出小区没有其他细胞是在较高的排，或者如果它们是在同一行，是该单元的左边。 这仍然给我们留下了太多的结果，但是。 考虑只在（3,4）和（4,4）的2细胞片：
StartX StartY X Y Visited
3 4 3 4 (3,4) ******
3 4 4 4 (3,4)(4,4)
4 4 4 4 (4,4)
4 4 3 4 (4,4)(3,4) ******
2行保持与“第一XY坐标”的（3,4），标记为****** 。 我们只需要一个行，因此我们使用ROW_NUMBER和，因为我们的编号，我们还不如去最长的Visited路径，这将给予我们许多细胞的片内，我们可以得到的。
最终外部查询简单地取从每个相似（X，Y）组中的第一行（RN = 1）。
为了显示每一块的所有小区，改变线
select X, Y, Visited
在中间
select X, Y, (
select distinct '('+right(StartX,3)+','+right(StartY,3)+')'
from Walker
where X=Z.X and Y=Z.Y
for xml path('')
) PieceCells
这给该输出
X Y PieceCells
1 1 (1,1)(2,1)(2,2)(3,2)
3 4 (3,4)(4,4)
5 6 (5,6)
7 5 (7,5)(8,5)(9,5)
8 1 (10,1)(8,1)(8,2)(9,1)(9,2)(9,3)
Answer 2:
好。 它有点硬。 但在任何情况下，我敢肯定，在一个简单的方法这个问题不能得到解决。 因此，我们有表：
CREATE Table Tbl1(Id int, X int, Y int)
INSERT INTO Tbl1
SELECT 1,1,1 UNION ALL
SELECT 2,1,2 UNION ALL
SELECT 3,1,8 UNION ALL
SELECT 4,1,9 UNION ALL
SELECT 5,1,10 UNION ALL
SELECT 6,2,2 UNION ALL
SELECT 7,2,3 UNION ALL
SELECT 8,2,8 UNION ALL
SELECT 9,2,9 UNION ALL
SELECT 10,3,9 UNION ALL
SELECT 11,4,3 UNION ALL
SELECT 12,4,4 UNION ALL
SELECT 13,5,7 UNION ALL
SELECT 14,5,8 UNION ALL
SELECT 15,5,9 UNION ALL
SELECT 16,6,5
这里是选择查询
with cte1 as
/*at first we make recursion to define groups of filled adjacent cells*/
/*as output of cte we have a lot of strings like <X>cell(1)X</X><Y>cell(1)Y</Y>...<X>cell(n)X</X><Y>cell(n)Y</Y>*/
(
SELECT id,X,Y,CAST('<X>'+CAST(X as varchar(10))+'</X><Y>'+CAST(Y as varchar(10))+'</Y>' as varchar(MAX)) info
FROM Tbl1
UNION ALL
SELECT b.id,a.X,a.Y,CAST(b.info + '<X>'+CAST(a.X as varchar(10))+'</X><Y>'+CAST(a.Y as varchar(10))+'</Y>' as varchar(MAX))
FROM Tbl1 a JOIN cte1 b
ON ((((a.X=b.X+1) OR (a.X=b.X-1)) AND a.Y=b.Y) OR (((a.Y=b.Y+1) OR (a.Y=b.Y-1)) AND a.X=b.X))
AND a.id<>b.id
AND
b.info NOT LIKE
('%'+('<X>'+CAST(a.X as varchar(10))+'</X><Y>'+CAST(a.Y as varchar(10))+'</Y>')+'%')
),
cte2 as
/*In this query, we select only the longest sequence of cell connections (first filter)*/
/*And we convert the string to a new standard (x,y | x,y | x,y |...| x,y) (for further separation)*/
(
SELECT *, ROW_NUMBER()OVER(ORDER BY info) cellGroupId
FROM(
SELECT REPLACE(REPLACE(REPLACE(REPLACE(info,'</Y><X>','|'),'</X><Y>',','),'<X>',''),'</Y>','') info
FROM(
SELECT info, MAX(LEN(info))OVER(PARTITION BY id)maxlen FROM cte1
) AS tmpTbl
WHERE maxlen=LEN(info)
)AS tmpTbl
),
cte3 as
/*In this query, we separated strings like (x,y | x,y | x,y |...| x,y) to many (x,y)*/
(
SELECT cellGroupId, CAST(LEFT(XYInfo,CHARINDEX(',',XYInfo)-1) as int) X, CAST(RIGHT(XYInfo,LEN(XYInfo)-CHARINDEX(',',XYInfo)) as int) Y
FROM(
SELECT cellGroupId, tmpTbl2.n.value('.','varchar(MAX)') XYinfo
FROM
(SELECT CAST('<r><c>' + REPLACE(info,'|','</c><c>')+'</c></r>' as XML) n, cellGroupId FROM cte2) AS tmpTbl1
CROSS APPLY n.nodes('/r/c') tmpTbl2(n)
) AS tmpTbl
),
cte4 as
/*In this query, we finally determined group of individual objects*/
(
SELECT cellGroupId,X,Y
FROM(
SELECT cellGroupId,X,Y,ROW_NUMBER()OVER(PARTITION BY X,Y ORDER BY cellGroupId ASC)rn
FROM(
SELECT *,
MAX(SumOfAdjacentCellsByGroup)OVER(PARTITION BY X,Y) Max_SumOfAdjacentCellsByGroup_ByXY /*calculated max value of <the sum of the cells in the group> by each cell*/
FROM(
SELECT *, SUM(1)OVER(PARTITION BY cellGroupId) SumOfAdjacentCellsByGroup /*calculated the sum of the cells in the group*/
FROM cte3
)AS TmpTbl
)AS TmpTbl
/*We got rid of the subgroups (i.e. [(1,2)(2,2)(2,3)] its subgroup of [(1,2)(1,1)(2,2)(2,3)])*/
/*it was second filter*/
WHERE SumOfAdjacentCellsByGroup=Max_SumOfAdjacentCellsByGroup_ByXY
)AS TmpTbl
/*We got rid of the same groups (i.e. [(1,1)(1,2)(2,2)(2,3)] its same as [(1,2)(1,1)(2,2)(2,3)])*/
/*it was third filter*/
WHERE rn=1
)
SELECT X,Y /*result*/
FROM(SELECT a.X,a.Y, ROW_NUMBER()OVER(PARTITION BY cellGroupId ORDER BY id)rn
FROM cte4 a JOIN Tbl1 b ON a.X=b.X AND a.Y=b.Y)a /*connect back*/
WHERE rn=1 /*first XY coordinate*/
Answer 3:
让我们假设你的坐标存储在X，Y的形式，这样的事情：
CREATE Table Puzzle(
id int identity, Y int, X int)
INSERT INTO Puzzle VALUES
(1,1),(1,2),(1,8),(1,9),(1,10),
(2,2),(2,3),(2,8),(2,9),
(3,9),
(4,3),(4,4),
(5,7),(5,8),(5,9),
(6,5)
然后，该查询显示板形式的难题（在文本模式下在SQL Management Studio中运行）
SELECT (
SELECT (
SELECT CASE WHEN EXISTS (SELECT *
FROM Puzzle T
WHERE T.X=X.X and T.Y=Y.Y)
THEN 'X' ELSE '.' END
FROM (values(0),(1),(2),(3),(4),(5),
(6),(7),(8),(9),(10),(11)) X(X)
ORDER BY X.X
FOR XML PATH('')) + Char(13) + Char(10)
FROM (values(0),(1),(2),(3),(4),(5),(6),(7)) Y(Y)
ORDER BY Y.Y
FOR XML PATH(''), ROOT('a'), TYPE
).value('(/a)[1]','varchar(max)')
它给你这个
............
.XX.....XXX.
..XX....XX..
.........X..
...XX.......
.......XXX..
.....X......
............
该查询分4个阶段进行，将让你的左上单元格的结果，如果你把它定义为最上面一行的最左边的单元格。
-- the first table expression joins cells together on the Y-axis
;WITH FlattenOnY(Y,XLeft,XRight) AS (
-- start with all pieces
select Y,X,X
from puzzle
UNION ALL
-- keep connecting rightwards from each cell as far as possible
select B.Y,A.XLeft,B.X
from FlattenOnY A
join puzzle B on A.Y=B.Y and A.XRight+1=B.X
)
-- the second table expression flattens the results from the first, so that
-- it represents ALL the start-end blocks on each row of the Y-axis
,YPieces(Y,XLeft,XRight) as (
--
select Y,XLeft,Max(XRight)
from(
select Y,Min(XLeft)XLeft,XRight
from FlattenOnY
group by XRight,Y)Z
group by XLeft,Y
)
-- here, select * from YPieces will return the "blocks" such as
-- Row 1: 1-2 & 8-10
-- Row 2: 2-3 (equals Y,XLeft,XRight of 2,2,3)
-- etc
-- the third expression repeats the first, except it now combines on the X-axis
,FlattenOnX(Y,XLeft,CurPieceXLeft,CurPieceXRight,CurPieceY) AS (
-- start with all pieces
select Y,XLeft,XLeft,XRight,Y
from YPieces
UNION ALL
-- keep connecting rightwards from each cell as far as possible
select A.Y,A.XLeft,B.XLeft,B.XRight,B.Y
from FlattenOnX A
join YPieces B on A.CurPieceY+1=B.Y and A.CurPieceXRight>=B.XLeft and B.XRight>=A.CurPieceXLeft
)
-- and again we repeat the 2nd expression as the 4th, for the final pieces
select Y,XLeft X
from (
select *, rn2=row_number() over (
partition by Y,XLeft
order by CurPieceY desc)
from (
select *, rn=row_number() over (
partition by CurPieceXLeft, CurPieceXRight, CurPieceY
order by Y)
from flattenOnX
) Z1
where rn=1) Z2
where rn2=1
其结果是
Y X
----------- -----------
1 1
1 8
4 3
5 7
6 5
或者是你的扁平状像这样的代表性？ 如果是这样，给我们留言，我会重做解决方案
create table Puzzle (
row int,
[0] bit, [1] bit, [2] bit, [3] bit, [4] bit, [5] bit,
[6] bit, [7] bit, [8] bit, [9] bit, [10] bit, [11] bit
)
insert Puzzle values
(0,0,0,0,0,0,0,0,0,0,0,0,0),
(1,0,1,1,0,0,0,0,0,1,1,1,0),
(2,0,0,1,1,0,0,0,0,1,1,0,0),
(3,0,0,0,0,0,0,0,0,0,1,0,0),
(4,0,0,0,1,1,0,0,0,0,0,0,0),
(5,0,0,0,0,0,0,0,1,1,1,0,0),
(6,0,0,0,0,0,1,0,0,0,0,0,0),
(7,0,0,0,0,0,0,0,0,0,0,0,0)
文章来源: View to identify grouped values or object
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://i.stack.imgur.com/fQ5ZI.png', '推荐 走好不送 的文章《查看，以确定分组的值或对象(View to identify grouped values or o》','https://www.manongdao.com/article-1244721.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}