在同一查询中使用查询结果 [英] Using results from a query within the same query
问题描述
我在MS Access 2010中有一个相当大的SQL语句.它在这里:
I have a rather big SQL statement that I am working with in MS Access 2010. Here it goes:
SELECT
W.ID AS wid,
W.wpt_ty AS ty,
W.wpt_num AS num,
W.wpt_nxt AS nxt,
W.latdeg AS lat,
W.londeg AS lon,
W.alt AS alt,
W.mission_id AS mid,
W.ctg1 AS ctg1,
W.ctg2 AS ctg2,
W.ctg3 AS ctg3,
W.ctg4 AS ctg4,
W.wpt_index AS indx,
W.vel AS vel,
W.tu AS tu,
R.route_num AS rnum,
R.AC_num AS ac,
R.route_type AS rtype,
R.LastUpdatedOn AS d8,
R.LastUpdatedBy AS auth,
R.flight_wpt_count AS wfcount,
M.mission_name AS msnName,
V.Description AS vstatus,
R.disallowed_reason_id AS did,
CW.wpt_num AS c1num,
CR.matching_route_id AS c1mrid,
CW.wpt_index AS c1indx,
CRU.runway_name AS c1rnwy,
CR.route_num AS c1rnum
FROM Validation AS V
(RIGHT JOIN Runways AS CRU
INNER JOIN (Routes CR
INNER JOIN Waypoints CW ON CR.ID = CW.route_id)
ON Runways.ID = Routes.runway_id
INNER JOIN ((Missions as M
INNER JOIN Routes AS R ON M.ID = R.mission_id)
INNER JOIN Waypoints AS W ON (R.ID = W.route_id)
AND (M.ID = W.mission_id)) ON
V.ID = R.validated
WHERE (((R.matching_route_id)=307543) AND ((R.validated) <> 0 ))
AND (((CW.mission_id)=mid) AND ((CW.wpt_num) = (ctg1))))
如果查看底部,则可以看到我在右连接上引用值ctg1
和mid
,而内部连接引用了其他文字值.最终,我将对ctg2
,ctg3
和ctg4
If you look at the bottom, you can see am I referencing the values ctg1
and mid
on a Right Join while the Inner Joins reference other literal values. Eventually I will want to do the same for ctg2
, ctg3
, and ctg4
现在我将它们作为2个独立的查询运行,但是发现它太慢了.如果我可以合并查询(类似于我在此处显示的方式),则可以大大加快处理速度.但是我不知道该怎么做:
Right now I am running these as 2 separate queries but finding it to be way too slow. If I can join combine the queries (sort of like how I am showing here) it could speed things up greatly. But I am at a loss for how to:
- 在内部/左联接中使用上述查询中的较早选择值,并将其推入右联接中所需的值.
- 我可能没有正确使用联接,但是我认为它们与合并可能来自相同表,只是在不同枢轴点上的数据有关.
- 如何使用MS Access GUI来帮助编写这样的查询.
- 我知道这是针对MS Access的,但是我正在为MySQL标记,以防万一有类似的查询可以移植到MS Access?
推荐答案
您是否尝试过使用UNION?
Have you tried using UNION for this?
它将允许您执行此查询(作为两个查询,您可能会提到),并将结果合并为输出.
It would allow you to execute this query (As two queries, which you mentioned as a possibility), and join the results for your output.
请注意,它会耗尽结果集中的重复项(仅显示其中之一).
Be warned, it will eat up (only show one of) your duplicates in the results set.
我还建议您为自己的利益阅读不同类型的联接,其答案如下:
I also suggest reading up on the different types of joins for your own benefit, in the following answer:
这篇关于在同一查询中使用查询结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!