帮助复杂的SQL [英] Help with complex SQL

问题描述

在我的RouteStage表中，路由1的定义与以下阶段相同
阶段ID SequenceNumber
1 --------------- 1
7 --------------- 2
3 --------------- 3
5 --------------- 4
X旅客从1号线沿着第一阶段的路线行驶到第三阶段
然后，我的神话用户决定删除阶段7
但我不想允许它，因为票价由:
组成 阶段1:登机关税
阶段7:Transit Tarrif
阶段3:从Tarrif出发
PassengerHistory包含PassengerID，RouteID，StartStage和EndStage.
我正在尝试合并一个查询，以将WayneHistory与RouteStage结合在一起，以使我可以检测到已经使用了第7阶段.
MySQL很有帮助地告诉我我有错误1064-SQL语法错误
这就是我的努力:

Route 1 is defined as with the following stages on my RouteStage table
Stage ID SequenceNumber
1---------------1
7---------------2
3---------------3
5---------------4
Passenger X travels from along Route 1 from Stage 1 to Stage 3
Then my mythical user decides to delete Stage 7
But I dont want to allow it because the fare is composed of :
Stage 1: Boarding Tarriff
Stage 7: Transit Tarrif
Stage 3: Jumping off Tarrif
PassengerHistory holds PassengerID, RouteID, StartStage and EndStage.
I am trying to but together a query to join PassengerHistory with RouteStage in a way that I can detect that stage 7 has been used
MySQL helpfully tells my that I have Error 1064 - error in SQL syntax
This is what my effort looks like:

select t1.route_ID, t1.stage_id, t1.end_stage_id from passengerhistory t1, routestage t2
where t1.route_id = t2.route_id
and t2.stage_id = 7;
and t2.sequenceNumber >= (select sequencenumber from routestage
where route_id = t2.route_id
and stage_id = t2.stage_id);

我想强迫我的用户在此routestage记录上添加结束日期，并定义一个新日期，以在第二天开始但不使用第7步，这样
所有已发行并包含阶段7的票证都不会丢失该信息.

I want to force my user to put an end date on this routestage record and define a new one to commence the following day without stage 7 so that
all tickets already issued incorporating stage 7 do not loose that information.

推荐答案

我怀疑错误是嵌入式select可能返回多行.通常我会有一个嵌入式选择，像这样

I suspect that the error is that the embedded select may return multiple rows. Usually I will have an embedded select looking like this

and t2.sequenceNumber = ( select min(sequencenumber) from routestage

不知道这是否会对您有帮助. :)

Not sure if that is going to help you though. :)

这篇关于帮助复杂的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！