MySQL 查询可以提取我正在寻找的数据吗?
在我正在进行的项目中,我被 Hades 的表结构所困扰。要记住两件事:
- 我现在无法更改表结构。我暂时被困住了。
- 查询是动态生成的,而不是硬编码的。因此,当我要求一个可以提取这些数据的查询时,我真正致力于的是一种能够生成我需要的查询的算法。
希望我能解释这个问题,而不会让你目光呆滞、大脑崩溃。
我们有一个实例表,看起来(简化)如下:
Instances InstanceID active 1 Y 2 Y 3 Y 4 N 5 Y 6 Y
然后,有多个数据表:
Table1 InstanceID field1 reference_field2 1 John 5 2 Sally NULL 3 Fred 6 4 Joe NULL Table2 InstanceID field3 5 1 6 1 Table3 InstanceID fieldID field4 5 1 Howard 5 2 James 6 2 Betty
请注意,Table1 中的reference_field2 包含对另一个实例的引用。 表2中的Field3稍微复杂一些。它包含表 3 的 fieldID。
我需要一个查询,该查询将为我提供一个列表,如下所示:
InstanceID field1 field4 1 John Howard 2 Sally 3 Fred
问题是,在我当前的查询中,我没有得到 Fred,因为 Table3 中没有 fieldID 1 的条目,并且InstanceID 6。 因此,到目前为止我能够得到的最好列表是
InstanceID field1 field4 1 John Howard 2 Sally
本质上,如果表 1 中存在字段 2 的条目,并且表 3 中没有条目包含字段 2 中包含的实例 ID,并且field3中包含字段ID,我没有从field1获取数据。
我一直在查看连接,直到我脸色发青,并且我看不到一种方法来处理 table3 没有条目的情况。
On the project I am working on, I am stuck with the table structure from Hades. Two things to keep in mind:
- I can't change the table structure right now. I'm stuck with it for the time being.
- The queries are dynamically generated and not hard coded. So, while I am asking for a query that can pull this data, what I am really working toward is an algorithm that will generate the query I need.
Hopefully, I can explain the problem without making your eyes glaze over and your brain implode.
We have an instance table that looks (simplified) along these lines:
Instances InstanceID active 1 Y 2 Y 3 Y 4 N 5 Y 6 Y
Then, there are multiple data tables along these lines:
Table1 InstanceID field1 reference_field2 1 John 5 2 Sally NULL 3 Fred 6 4 Joe NULL Table2 InstanceID field3 5 1 6 1 Table3 InstanceID fieldID field4 5 1 Howard 5 2 James 6 2 Betty
Please note that reference_field2 in Table1 contains a reference to another instance.
Field3 in Table2 is a bit more complicated. It contains a fieldID for Table 3.
What I need is a query that will get me a list as follows:
InstanceID field1 field4 1 John Howard 2 Sally 3 Fred
The problem is, in the query I currently have, I do not get Fred because there is no entry in Table3 for fieldID 1 and InstanceID 6. So, the very best list I have been able to get thus far is
InstanceID field1 field4 1 John Howard 2 Sally
In essence, if there is an entry in Table1 for Field 2, and there is not an entry in Table 3 that has the instanceID contained in field2 and the field ID contained in field3, I don't get the data from field1.
I have looked at joins till I'm blue in the face, and I can't see a way to handle the case when table3 has no entry.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
LEFT JOIN...
两个左连接应该处理没有其他行的情况...
LEFT JOIN...
The two left joins should handle the case where there are no other rows...
如果您发布了您的查询,这将会有所帮助,因为我认为您在此处的表描述中存在一些错误,因此不太清楚表是如何连接的。
无论如何,您的查询中可能有一个内部联接(通常写为
JOIN
)。将其替换为左外连接 (LEFT JOIN
)。它不需要正确的表来包含该行并返回 NULL 而不是实际值。It would help if you posted the query you have, because I think you have some mistakes in the table descriptions here, so it's not very clear how are the tables connected.
Anyway, you probably have an inner join in your query (normally written as just
JOIN
). Replace it with a left outer join (LEFT JOIN
). It will not require the right table to contain the row and returnNULL
instead of the actual value.