续前面的问题，每艘船上有很多座位，怎样检测座位是否可以预订

[efstudy2014]

接续我之前已解决的问题：有很多条航线，每条航线经过若干个港口，现在，有一批货要从A港运到B港，寻找合适的航线。只要航线经过的港口中，先经过A再经过B，就是合适的航线。
http://bbs.csdn.net/topics/390882324
以上问题已解决。继续问：

如果每艘船上有很多座位，怎样检测坐位是否可以预订？把之前的表和数据列出来
港口表
港口ID 名称
1 A
2 B
3 C
4 D
5 E
---------------
航线表
航线ID 代号
1 MX50
2 ZD31
----------------
路由表
航线ID 港口ID 顺序
1 1 1
1 3 2
1 2 3
2 5 1
2 4 2
2 3 3
2 2 4
2 1 5
----------------
座位表
座位ID 航线ID 座号
1 1 1A
2 1 1B
3 1 1C
..............................
订单表
订单ID 座位ID 出发港ID 目的港ID
1 2 3 2

客户要预订座位，要乘坐航线ID1，从港口ID1到港口ID2（路由1→3→2），需要找出某航线的哪些座位可以出售？
如上数据，座位2不能出售，因为它3→2这一段已经被人预订了
求SQL

[bw555]

设定出发港为P_S,目的港为P_D

SELECT 座位表.* FROM (
    SELECT A.航线ID,A.顺序 SINDEX,B.顺序 DINDEX FROM 路由表 A,路由表 B
    WHERE A.航线ID=B.航线ID AND A.港口ID=P_S AND B.港口ID=P_D AND A.顺序<B.顺序
    )T1, 座位表 T2
WHERE  T1.航线ID=T2.航线ID
AND NOT EXISTS (SELECT * FROM 订单表 A,座位表 B,路由表 C,路由表 D
    WHERE A.座位ID=B. 座位ID
    AND B.航线ID=C.航线ID
    AND B.航线ID=D.航线ID 
    AND A.出发港ID=C.港口ID
    AND A.目的港ID=D.港口ID
    AND A.座位ID=T2.座位ID
    AND C.顺序<=T1.DINDEX
    AND D.顺序>=T1.SINDEX)

[bw555]

上面是oracle的写法，部分语法可能不一样，参考下思路吧

[jayzhihui]

/*
客户要预订座位，要乘坐航线ID1，从港口ID1到港口ID2（路由1→3→2），需要找出某航线的哪些座位可以出售？
如上数据，座位2不能出售，因为它3→2这一段已经被人预订了
求SQL
更多 0
*/


if OBJECT_ID('tempdb..#港口表')>0
  drop table #港口表 
create table #港口表
(
  港口ID int,
  名称 NVARCHAR(20)
);
insert into #港口表 values
(
1,'A'),
(2,'B'),
(3,'C'),
(4,'D'),
(5,'E'
);
if OBJECT_ID('tempdb..#航线表')>0
  drop table #航线表 
create table #航线表
(
  航线ID int,
  代号 NVARCHAR(20)
);
insert into #航线表 values
(
1,'MX50'),
(2,'ZD31'
);

if OBJECT_ID('tempdb..#路由表')>0
  drop table #路由表 
create table #路由表
(
航线ID int, 港口ID int,  顺序 int
) ;
insert into #路由表 values
(1,            1,          1),
(1,            3,         2),
(1,            2,           3),
(2,            5,           1),
(2,            4,           2),
(2,            3,           3),
(2,            2,          4),
(2,            1,          5);

if OBJECT_ID('tempdb..#座位表')>0
  drop table #座位表

CREATE TABLE #座位表
(座位ID INT,  航线ID INT,   座号 VARCHAR(10));
INSERT INTO #座位表 VALUES
(1,             1,          '1A'),
(2,             1,          '1B'),
(3,             1,          '1C');

if OBJECT_ID('tempdb..#订单表')>0
  drop table #订单表
  
CREATE TABLE #订单表
(订单ID INT, 座位ID INT, 出发港ID INT, 目的港ID INT);

INSERT INTo #订单表 values
(1,            2,           3,                2)


/*
客户要预订座位，要乘坐航线ID1，从港口ID1到港口ID2（路由1→3→2），需要找出某航线的哪些座位可以出售？
如上数据，座位2不能出售，因为它3→2这一段已经被人预订了
求SQL
更多 0
*/


declare @s港口id int = 3,   --出发港口
		@e港口id int = 2,   --到达港口
		@航线id int = 1,	--要乘坐的航线
		@线路 varchar(100)  --线路判断是否符合要求
		 
select @线路 = '%' + cast(@s港口id as varchar(10)) + '-' + cast(@e港口id as varchar(10)) +'%' ; 

if OBJECT_ID('tempdb..#航线路由')>0
  drop table #航线路由;
with cte as --获取路由线路
(
	SELECT STUFF(b.v.value('/r[1]','varchar(100)'),1,1,'') 线路
    FROM
    (SELECT v=(SELECT '-' + cast(港口ID as varchar(10)) FROM #路由表 where 航线ID = @航线id FOR XML PATH(''),ROOT('r'),TYPE)) b
)
select * into #航线路由 from cte

if not exists (select * from #航线路由 where 线路 like @线路)
begin
  print '此航线无从' + @线路 +'路径，请选择其他航线！'
end
else
begin
  select * from #座位表 a
  where not exists (select * from #订单表 where 座位ID = a.座位ID)
end

[jayzhihui]

有点BUG，修正一下

/*
客户要预订座位，要乘坐航线ID1，从港口ID1到港口ID2（路由1→3→2），需要找出某航线的哪些座位可以出售？
如上数据，座位2不能出售，因为它3→2这一段已经被人预订了
求SQL
更多 0
*/


if OBJECT_ID('tempdb..#港口表')>0
  drop table #港口表 
create table #港口表
(
  港口ID int,
  名称 NVARCHAR(20)
);
insert into #港口表 values
(
1,'A'),
(2,'B'),
(3,'C'),
(4,'D'),
(5,'E'
);
if OBJECT_ID('tempdb..#航线表')>0
  drop table #航线表 
create table #航线表
(
  航线ID int,
  代号 NVARCHAR(20)
);
insert into #航线表 values
(
1,'MX50'),
(2,'ZD31'
);

if OBJECT_ID('tempdb..#路由表')>0
  drop table #路由表 
create table #路由表
(
航线ID int, 港口ID int,  顺序 int
) ;
insert into #路由表 values
(1,            1,          1),
(1,            3,         2),
(1,            2,           3),
(2,            5,           1),
(2,            4,           2),
(2,            3,           3),
(2,            2,          4),
(2,            1,          5);

if OBJECT_ID('tempdb..#座位表')>0
  drop table #座位表

CREATE TABLE #座位表
(座位ID INT,  航线ID INT,   座号 VARCHAR(10));
INSERT INTO #座位表 VALUES
(1,             1,          '1A'),
(2,             1,          '1B'),
(3,             1,          '1C');

if OBJECT_ID('tempdb..#订单表')>0
  drop table #订单表
  
CREATE TABLE #订单表
(订单ID INT, 座位ID INT, 出发港ID INT, 目的港ID INT);

INSERT INTo #订单表 values
(1,            2,           3,                2)


/*
客户要预订座位，要乘坐航线ID1，从港口ID1到港口ID2（路由1→3→2），需要找出某航线的哪些座位可以出售？
如上数据，座位2不能出售，因为它3→2这一段已经被人预订了
求SQL
更多 0
*/


declare @s港口id int = 3,   --出发港口
		@e港口id int = 2,   --到达港口
		@航线id int = 1,	--要乘坐的航线
		@线路 varchar(100)  --线路判断是否符合要求
		 
select @线路 = '%-' + cast(@s港口id as varchar(10)) + '-%-' + cast(@e港口id as varchar(10)) +'%' ; 

if OBJECT_ID('tempdb..#航线路由')>0
  drop table #航线路由;
with cte as --获取路由线路
(
	SELECT b.v.value('/r[1]','varchar(100)') 线路
    FROM
    (SELECT v=(SELECT '-' + cast(港口ID as varchar(10)) + '-' FROM #路由表 where 航线ID = @航线id FOR XML PATH(''),ROOT('r'),TYPE)) b
)
select * into #航线路由 from cte


if not exists (select * from #航线路由 where 线路 like @线路)
begin
  print '此航线无从' + @线路 +'路径，请选择其他航线！'
end
else
begin
  select * from #座位表 a
  where not exists (select * from #订单表 where 座位ID = a.座位ID)
end

[xiaodongni]

把出发港和目的港口ID 换成顺序ID 其实就是判断新增订单的同航线，同座位 不能存在重合 等价于判断2个时间段是否重合问题。

[Tiger_Zhao]

WITH 路由表(航线ID,港口ID,顺序) AS (
    SELECT 1,1,1 UNION ALL
    SELECT 1,3,2 UNION ALL
    SELECT 1,2,3 UNION ALL
    SELECT 2,5,1 UNION ALL
    SELECT 2,4,2 UNION ALL
    SELECT 2,3,3 UNION ALL
    SELECT 2,2,4 UNION ALL
    SELECT 2,1,5
),
座位表(座位ID,航线ID,座号) AS (
    SELECT 1,1,'1A' UNION ALL
    SELECT 2,1,'1B' UNION ALL
    SELECT 3,1,'1C'
),
订单表(订单ID,座位ID,出发港ID,目的港ID) AS (
    SELECT 1,2,3,2
),
a AS (
    SELECT s.航线ID,
           o.座位ID,
           r1.顺序 顺序1,
           r2.顺序 顺序2
      FROM 订单表 o
      JOIN 座位表 s
        ON s.座位ID = o.座位ID
      JOIN 路由表 r1
        ON r1.航线ID = s.航线ID
       AND r1.港口ID = o.出发港ID
      JOIN 路由表 r2
        ON r2.航线ID = s.航线ID
       AND r2.港口ID = o.目的港ID
),
b AS (
    SELECT q.航线ID,
           r1.顺序 顺序1,
           r2.顺序 顺序2
      FROM (SELECT 1 航线ID,
                   1 出发港ID,
                   2 目的港ID
           ) q
      JOIN 路由表 r1
        ON r1.航线ID = q.航线ID
       AND r1.港口ID = q.出发港ID
      JOIN 路由表 r2
        ON r2.航线ID = q.航线ID
       AND r2.港口ID = q.目的港ID
),
c AS (
    SELECT b.航线ID,
           s.座位ID,
           s.座号,
           b.顺序1,
           b.顺序2
      FROM b
      JOIN 座位表 s
        ON s.航线ID = b.航线ID
)
SELECT 座号 
  FROM c
 WHERE NOT EXISTS (SELECT *
                     FROM a
                    WHERE a.航线ID = c.航线ID
                      AND a.座位ID = c.座位ID
                      AND (   (a.顺序1 <= c.顺序1 AND c.顺序1 < a.顺序2)
                           OR (a.顺序1 < c.顺序2 AND c.顺序2 <= a.顺序2)
                          )
                  )

座号
----
1A
1C