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 cteif not exists (select * from #航线路由 where 线路 like @线路) begin print '此航线无从' + @线路 +'路径,请选择其他航线!' end else begin select * from #座位表 a where not exists (select * from #订单表 where 座位ID = a.座位ID) end
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
设定出发港为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)
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
客户要预订座位,要乘坐航线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 cteif not exists (select * from #航线路由 where 线路 like @线路)
begin
print '此航线无从' + @线路 +'路径,请选择其他航线!'
end
else
begin
select * from #座位表 a
where not exists (select * from #订单表 where 座位ID = a.座位ID)
end
客户要预订座位,要乘坐航线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
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)
等价于判断2个时间段是否重合问题。
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