22,207
社区成员
发帖
与我相关
我的任务
分享
-- 查询
IF OBJECT_ID('tempdb..#tt') IS NOT NULL DROP TABLE #tt;
WITH
T AS(
SELECT A.*, k = A.起点, l = 1 FROM #t A
WHERE NOT EXISTS(
SELECT * FROM #t B
WHERE A.起点 = b.末点
)
UNION ALL
SELECT A.*, B.k, B.l+1
FROM #t A, T B
WHERE A.起点 = B.末点
)
SELECT * INTO #tt FROM T
;
DECLARE @l int, @sql nvarchar(max);
SELECT @l = MAX(l), @sql = N'' FROM #tt;
WHILE @l > 0
SELECT
@sql = N',起点 = MAX(CASE l WHEN ' + RTRIM(@l) + N' THEN 起点 END), 末点 = MAX(CASE l WHEN ' + RTRIM(@l) + N' THEN 末点 END)' + @sql,
@l = @l - 1
;
SET @sql = N'SELECT ' + STUFF(@sql, 1, 1, N'') + N' FROM #tt GROUP BY K';
EXEC(@sql);
DROP TABLE #tt;
-- 测试数据
CREATE TABLE #t(起点 varchar(10), 末点 varchar(10));
INSERT #t VALUES
(1, 2),
(2, 3),
(3, 4),
(4, 5),
(7, 9),
(9, 19),
(21, 22);
INSERT #t VALUES
('a', 'b'),
('c', 'dd'),
('dd', 'ee');
-- 查询
WITH
T AS(
SELECT A.*, k = A.起点, l = 1 FROM #t A
WHERE NOT EXISTS(
SELECT * FROM #t B
WHERE A.起点 = b.末点
)
UNION ALL
SELECT A.*, B.k, B.l+1
FROM #t A, T B
WHERE A.起点 = B.末点
)
SELECT
起点 = MAX(CASE l WHEN 1 THEN 起点 END), 末点 = MAX(CASE l WHEN 1 THEN 末点 END),
起点 = MAX(CASE l WHEN 2 THEN 起点 END), 末点 = MAX(CASE l WHEN 2 THEN 末点 END),
起点 = MAX(CASE l WHEN 3 THEN 起点 END), 末点 = MAX(CASE l WHEN 3 THEN 末点 END),
起点 = MAX(CASE l WHEN 4 THEN 起点 END), 末点 = MAX(CASE l WHEN 4 THEN 末点 END)
FROM T
GROUP BY K
;
DROP TABLE #t
create table #t(start_point varchar(10),end_point varchar(10))
go
insert into #t
select '1','2' union
select '2','3' union
select '3','4' union
select '4','5' union
select 'a','b'
with cte
as
(select *,start_point as parent_point from #t A where not exists (select 1 from #t where A.start_point=end_point)
union all
select A.*,B.parent_point
from #t A
join cte B ON A.start_point=B.end_point
)
select *,ROW_NUMBER() over (partition by parent_point order by start_point) as seq into #t1 from cte
declare @sql nvarchar(max)
select @sql=ISNULL(@sql+',','')+'max(case when seq='+cast(seq as nvarchar)+' then start_point else '''' end) as start_point'+CAST(seq as nvarchar)+',max(case when seq='+cast(seq as nvarchar)+' then end_point else '''' end) as end_point'+CAST(seq as nvarchar)+''
from
#t1 as A
group by seq
set @sql='select parent_point,'+@sql+' from #t1 group by parent_point'
exec(@sql)
drop table #t1
use Tempdb
go
--> --> 听雨停了-->生成测试数据
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([起点] nvarchar(21),[末点] nvarchar(21))
Insert #tab
select N'1',N'2' union all
select N'2',N'3' union all
select N'3',N'4' union all
select N'4',N'5' union all
select N'a',N'b' UNION ALL
select N'b',N'c' UNION ALL
select N'x',N'y'
GO
--测试数据结束
--分类出哪些数据是连接的
;WITH cte AS (
SELECT a.起点 as a,b.起点 as b FROM #tab a
right JOIN #tab b ON a.末点=b.起点
)
,cte2 AS (
SELECT b FROM cte WHERE ISNULL(a,'')=''
)
,cte3 AS (
SELECT a.*,NEWID() AS [TYPE] FROM #tab a
INNER JOIN cte2 b ON a.起点=b.b
)
,cte4 AS (
SELECT * FROM cte3
UNION ALL
SELECT a.*,b.type FROM #tab A
INNER JOIN cte4 b ON a.起点=b.末点
)
--把分类的结果插入表tab_2中
SELECT * FROM cte4
GO
--SELECT * FROM tab_2 ORDER BY TYPE,起点
--起点 末点 TYPE
----------------------- --------------------- ------------------------------------
--x y 5AC61776-1D28-4537-A89C-0997795098A8
--a b E1A045D0-4085-4340-A4D7-6A8F4A6BB7CA
--b c E1A045D0-4085-4340-A4D7-6A8F4A6BB7CA
--1 2 7117363C-6A25-4196-B08A-718E15811415
--2 3 7117363C-6A25-4196-B08A-718E15811415
--3 4 7117363C-6A25-4196-B08A-718E15811415
--4 5 7117363C-6A25-4196-B08A-718E15811415
--合并起点末点然后排序,最后把这个结果行转列显示
SELECT 起点 FROM tab_2
WHERE TYPE='7117363C-6A25-4196-B08A-718E15811415'
UNION ALL
SELECT 末点 FROM tab_2
WHERE TYPE='7117363C-6A25-4196-B08A-718E15811415' ORDER BY 起点
--起点
-----------------------
--1
--2
--2
--3
--3
--4
--4
--5
--把上面这个结果行转列显示,具体怎么操作就不写了,写了这么多太累了,你自己百度下