用SQL解七桥问题

加里宁格勒市中心有一条普雷戈里亚河，该河流穿过城市，并把城市分割成两个岛屿和两片陆地。这片土地在18世纪被称为柯尼斯堡，隶属于普鲁士。在当时，有一个和柯尼斯堡桥有关的问题：如何只把每座桥走一次而穿过整个城市？

下图是柯尼斯堡七座桥的简化图。

这个七桥问题后来被欧拉解决，以下是使用关系数据库来解题的过程。

1. 定义表和插入数据

LAND表
create table land (land varchar2(5),
constraint land_pk primary key (land))

insert into land values('N1');
insert into land values('N2');
insert into land values('N3');
insert into land values('N4');

 

BRIDGE表
create table bridge (bridge varchar2(5),
constraint bridge_pk primary key (bridge))

insert into bridge values('B1');
insert into bridge values('B2');
insert into bridge values('B3');
insert into bridge values('B4');
insert into bridge values('B5');
insert into bridge values('B6');
insert into bridge values('B7');

BRIDGE_LAND表
create table bridge_land (bridge varchar2(5),
land varchar2(5),
constraint b_fk foreign key (bridge) references bridge(bridge),
constraint l_fk foreign key (land) references land(land)
)

insert into bridge_land values('B1','N1');
insert into bridge_land values('B1','N3');
insert into bridge_land values('B2','N1');
insert into bridge_land values('B2','N4');
insert into bridge_land values('B3','N1');
insert into bridge_land values('B3','N3');
insert into bridge_land values('B4','N1');
insert into bridge_land values('B4','N4');
insert into bridge_land values('B5','N1');
insert into bridge_land values('B5','N2');
insert into bridge_land values('B6','N2');
insert into bridge_land values('B6','N3');
insert into bridge_land values('B7','N2');
insert into bridge_land values('B7','N4');

 

2. 用SQL语言写成的解法（比较笨拙的解法）

select t1.land,t2.bridge,t2.land,t3.bridge,t4.land,t5.bridge,t6.land,t7.bridge,t8.land,t9.bridge,t10.land,t11.bridge,t12.land,t13.bridge,t14.land
from bridge_land t1 join bridge_land t2 on (t2.bridge=t1.bridge and t2.land!=t1.land)
                    join bridge_land t3 on (t3.land=t2.land and t3.bridge!=t1.bridge and t3.bridge!=t2.bridge)
                    join bridge_land t4 on (t4.bridge=t3.bridge and t4.land!=t3.land)
                    join bridge_land t5 on (t5.land=t4.land and t5.bridge!=t2.bridge and t5.bridge!=t3.bridge)
                    join bridge_land t6 on (t6.bridge=t5.bridge and t6.land!=t5.land)
                    join bridge_land t7 on (t7.land=t6.land and t7.bridge!=t2.bridge and t7.bridge!=t3.bridge and t7.bridge!=t5.bridge)
                    join bridge_land t8 on (t8.bridge=t7.bridge and t8.land!=t7.land)
                    join bridge_land t9 on (t9.land=t8.land and t9.bridge!=t2.bridge and t9.bridge!=t3.bridge and t9.bridge!=t5.bridge and t9.bridge!=t7.bridge)
                    join bridge_land t10 on (t10.bridge=t9.bridge and t10.land!=t9.land)
                    join bridge_land t11 on (t11.land=t10.land and t11.bridge!=t2.bridge and t11.bridge!=t3.bridge and t11.bridge!=t5.bridge and t11.bridge!=t7.bridge and t11.bridge!=t9.bridge)
                    join bridge_land t12 on (t12.bridge=t11.bridge and t12.land!=t11.land)
                    join bridge_land t13 on (t13.land=t12.land and t13.bridge!=t2.bridge and t13.bridge!=t3.bridge and t13.bridge!=t5.bridge and t13.bridge!=t7.bridge and t13.bridge!=t9.bridge and t13.bridge!=t11.bridge)
                    join bridge_land t14 on (t14.bridge=t13.bridge and t14.land!=t13.land)
order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

这个查询没有结果返回，说明不重复的遍历七座桥是无解的。

3. 以下查询求得无重复遍历六座桥的全部欧拉路径
 

select t1.land,t2.bridge,t2.land,t3.bridge,t4.land,t5.bridge,t6.land,t7.bridge,t8.land,t9.bridge,t10.land,t11.bridge,t12.land
from bridge_land t1 join bridge_land t2 on (t2.bridge=t1.bridge and t2.land!=t1.land)
                    join bridge_land t3 on (t3.land=t2.land and t3.bridge!=t1.bridge and t3.bridge!=t2.bridge)
                    join bridge_land t4 on (t4.bridge=t3.bridge and t4.land!=t3.land)
                    join bridge_land t5 on (t5.land=t4.land and t5.bridge!=t2.bridge and t5.bridge!=t3.bridge)
                    join bridge_land t6 on (t6.bridge=t5.bridge and t6.land!=t5.land)
                    join bridge_land t7 on (t7.land=t6.land and t7.bridge!=t2.bridge and t7.bridge!=t3.bridge and t7.bridge!=t5.bridge)
                    join bridge_land t8 on (t8.bridge=t7.bridge and t8.land!=t7.land)
                    join bridge_land t9 on (t9.land=t8.land and t9.bridge!=t2.bridge and t9.bridge!=t3.bridge and t9.bridge!=t5.bridge and t9.bridge!=t7.bridge)
                    join bridge_land t10 on (t10.bridge=t9.bridge and t10.land!=t9.land)
                    join bridge_land t11 on (t11.land=t10.land and t11.bridge!=t2.bridge and t11.bridge!=t3.bridge and t11.bridge!=t5.bridge and t11.bridge!=t7.bridge and t11.bridge!=t9.bridge)
                    join bridge_land t12 on (t12.bridge=t11.bridge and t12.land!=t11.land)
order by 1,2,3,4,5,6,7,8,9,10,11,12,13

可以查出256条结果，即存在256条欧拉路径

4. 以下查询求得不重复遍历六座桥的欧拉环

select t1.land,t2.bridge,t2.land,t3.bridge,t4.land,t5.bridge,t6.land,t7.bridge,t8.land,t9.bridge,t10.land,t11.bridge,t12.land
from bridge_land t1 join bridge_land t2 on (t2.bridge=t1.bridge and t2.land!=t1.land)
                    join bridge_land t3 on (t3.land=t2.land and t3.bridge!=t1.bridge and t3.bridge!=t2.bridge)
                    join bridge_land t4 on (t4.bridge=t3.bridge and t4.land!=t3.land)
                    join bridge_land t5 on (t5.land=t4.land and t5.bridge!=t2.bridge and t5.bridge!=t3.bridge)
                    join bridge_land t6 on (t6.bridge=t5.bridge and t6.land!=t5.land)
                    join bridge_land t7 on (t7.land=t6.land and t7.bridge!=t2.bridge and t7.bridge!=t3.bridge and t7.bridge!=t5.bridge)
                    join bridge_land t8 on (t8.bridge=t7.bridge and t8.land!=t7.land)
                    join bridge_land t9 on (t9.land=t8.land and t9.bridge!=t2.bridge and t9.bridge!=t3.bridge and t9.bridge!=t5.bridge and t9.bridge!=t7.bridge)
                    join bridge_land t10 on (t10.bridge=t9.bridge and t10.land!=t9.land)
                    join bridge_land t11 on (t11.land=t10.land and t11.bridge!=t2.bridge and t11.bridge!=t3.bridge and t11.bridge!=t5.bridge and t11.bridge!=t7.bridge and t11.bridge!=t9.bridge)
                    join bridge_land t12 on (t12.bridge=t11.bridge and t12.land!=t11.land)
where t1.land=t12.land

查不出结果，也就是不存在欧拉环

5. 以下查询求得每块陆地的桥的数量
SQL> select land,count(bridge) from bridge_land group by land;

LAND  COUNT(BRIDGE)
----- -------------
N1                5
N2                3
N4                3
N3                3