为了发这个帖子，我准备了一早上，换乘算法的sql请看下

Question

一个表，存在从A站点到B站点的所有的换乘方案，但是都是一种方案一条记录，现在想要显示成便于用户看的那种方式。
我尝试了Groupby，但是貌似不是单纯的group by可以搞定的。弄了好几天，头都大了。希望各位大大给看看。已有的基础表如下图
想要的结果如下图
大体的需求，就是根据 首乘站数加上换乘站数，如果相等，就看看 首乘线路 中转站 换乘线路 这些，是不是有合并的可能。我做了测试代码，如下
create table AAAA
(
id int identity(1,1),始发站 varchar(4),首乘线路 varchar(10),首乘站数 smallint,
中转站 varchar(4),换乘线路 varchar(10),换乘站数 smallint,终点站 varchar(4)
) insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',5,'2166',  '119A',6,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',5,'2166',  '114A',6,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',3,'2168',  '119A',8,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',3,'2168',  '114A',8,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',9,'2173',  '119A',2,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',9,'2173',  '114A',2,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',4,'2327',  '119A',7,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',4,'2327',  '114A',7,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',7,'2664',  '119A',4,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',7,'2664',  '114A',4,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',2,'2713',  '119A',9,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',2,'2713',  '114A',9,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',8,'2714',  '119A',3,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',8,'2714',  '114A',3,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','125A',6,'2841',  '114A',5,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','363A',3,'2106',  '114A',13,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','605A',3,'2106',  '114A',13,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','318B',3,'2106',  '114A',13,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','378B',3,'2106',  '114A',13,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','363A',4,'2281',  '114A',14,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','605A',4,'2281',  '114A',14,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','318B',4,'2281',  '114A',14,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','378B',4,'2281',  '114A',14,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','605A',5,'2062',  '114A',15,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','318B',5,'2062',  '114A',15,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','378B',5,'2062',  '114A',15,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','363A',5,'2062',  '114A',15,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','368',7,'2062',  '114A',15,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','363A',8,'2187',  '114A',18,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','318B',8,'2187',  '114A',18,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','801',10,'2187',  '114A',18,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','368',10,'2187',  '114A',18,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','3B',10,'2187',  '114A',18,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','306B',10,'2187',  '114A',18,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','605B',10,'1828',  '119A',27,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','318',10,'1828',  '119A',27,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','368',10,'1828',  '119A',27,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','3A',12,'1827',  '119A',28,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','306A',12,'1827',  '119A',28,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','368',12,'2619',  '119A',29,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','368',13,'1857',  '119A',30,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','368',27,'1857',  '119A',30,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','368',30,'1828',  '119A',27,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','368',28,'2619',  '119A',29,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','801',44,'2187',  '114A',18,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','368',48,'2062',  '114A',15,'2172')
 insert into AAAA(始发站,首乘线路,首乘站数,中转站,换乘线路,换乘站数,终点站)   values('2531','368',51,'2187',  '114A',18,'2172')

Answer 1

目前，我用group by 配合自定义函数，实现了一半，但是太复杂了，而且想要全部实现，必须再建一个临时表，在对临时表groupby 和自定义函数。实现起来，太过复杂了，实用性太低。

Answer 2

谢谢楼上关注，我先抛砖引玉，说下我现在的做法，其实我也实现了，但是是最最笨的方法，先group by 一部分，得到结果，再进行一次group by 。代码如下，可以配合上面的表和数据使用。
CREATE FUNCTION fun_换乘线路 (@s1 varchar(4),@s2 varchar(4),@中转站 varchar(4),@换乘站数 smallint)  
RETURNS varchar(8000) AS  --返回两个站点间最短的直达站数的字符串BEGIN  declare @Result varchar(8000)

set @Result=''

--声明一个游标
DECLARE MyCURSOR CURSOR FOR 
select distinct 换乘线路 from AAAA where 始发站=@s1 and 终点站=@s2
and 换乘站数=@换乘站数 and 中转站=@中转站
order by 换乘线路

--打开游标
open MyCURSOR

--声明变量
declare @BusCode varchar(10)

--循环移动
fetch next from MyCURSOR into @BusCode 
while(@@fetch_status=0)
  begin
    set @Result=@Result + @BusCode + '路 '
    fetch next from MyCURSOR into @BusCode
  end

close MyCURSOR
deallocate MyCURSOR

return @ResultENDCREATE FUNCTION fun_首乘线路 (@s1 varchar(4),@s2 varchar(4),@首乘站数 smallint,@中转站 varchar(4))  
RETURNS varchar(8000) AS  --返回两个站点间最短的直达站数的字符串BEGIN  declare @Result varchar(8000)

set @Result=''

--声明一个游标
DECLARE MyCURSOR CURSOR FOR 
select distinct 首乘线路 from AAAA where 始发站=@s1 and 终点站=@s2 
and 首乘站数=@首乘站数 and 中转站=@中转站
order by 首乘线路

--打开游标
open MyCURSOR

--声明变量
declare @BusCode varchar(10)

--循环移动
fetch next from MyCURSOR into @BusCode 
while(@@fetch_status=0)
  begin
    set @Result=@Result + @BusCode + '路 '
    fetch next from MyCURSOR into @BusCode
  end

close MyCURSOR
deallocate MyCURSOR

return @ResultENDselect 首乘站数,dbo.fun_首乘线路('2531','2172',首乘站数,中转站),中转站,换乘站数,
dbo.fun_换乘线路('2531','2172',中转站,换乘站数), 首乘站数+换乘站数
from AAAA where 始发站='2531' and 终点站='2172' 
group by 首乘站数,中转站,换乘站数
order by 首乘站数+换乘站数,dbo.fun_首乘线路('2531','2172',首乘站数,中转站),dbo.fun_换乘线路('2531',中转站,换乘站数), 首乘站数,换乘站数
有了上面两个函数，运行最后面得sql语句，你就会看到实现了一半的结果，有了这个结果，得到图上的结果就不难了，但是这个过程有点复杂。不实用。

Answer 3

是不是这样：with cte as
(
select 首乘线路,中转站,c1=count(*),c2=min(首乘站数),c3=max(换乘站数)
,c4=stuff((select distinct ','+换乘线路 from AAAA where 首乘线路=a.首乘线路 and 中转站=a.中转站 for xml path('')),1,1,'') 
,c5=首乘站数+换乘站数
from AAAA a 
group by 首乘线路,中转站,首乘站数+换乘站数
)
select 乘坐=stuff((select distinct ','+首乘线路 from cte where c4=a.c4 and c5=a.c5 for xml path('')),1,1,'')
,站数=min(c2),中转站=stuff((select distinct ','+中转站 from cte where c4=a.c4 and c5=a.c5 for xml path('')),1,1,'') 
,再换乘=c4,站数=max(c3)
from cte a 
group by c4,c5/*
乘坐 站数 中转站 再换乘 站数
------------------- ------- ----------------------------------- ----------- ------
125A 6 2841 114A 5
125A 2 2166,2168,2173,2327,2664,2713,2714 114A,119A 9
318B,363A,378B,605A 3 2106 114A 13
318B,363A,378B,605A 4 2281 114A 14
318B,363A,378B,605A 5 2062 114A 15
368 7 2062 114A 15
318B,363A 8 2187 114A 18
306B,368,3B,801 10 2187 114A 18
318,368,605B 10 1828 119A 27
306A,3A 12 1827 119A 28
368 12 2619 119A 29
368 13 1857 119A 30
368 27 1828,1857,2619 119A 30
801 44 2187 114A 18
368 48 2062 114A 15
368 51 2187 114A 18(16 行受影响)
*/

Answer 4

9楼的高人， 
coleling
 
(气死我也，今后只答TSQL问题) 我激动不过你的代码是sql2000的吗？我是在sql2000下用的，运行报错呢

Answer 5

哎，原来是2005的方法，这是我的错误，我没说清楚，我是用的sql2000,ai....

Answer 6

恭喜，coleling一般都会让我们激动的。

Answer 7

思路应该清楚了三，就是两个Group by而已，你自己写函数，把字符串连接起来就行了嘛。2000的语句真是不想写了，写了N年，写伤了...