棘手的 SQL SELECT 问题 (MySQL)
拥有此表(抱歉,这里似乎无法获得合适的布局):
PD Header Text Mask_Producer Mask_Dep Mask_Diam
----------------------------------------------------------------------------------------------
10 Producer Aproducer Aprod * *
10 Producer Bproducer Bprod * *
20 Diam A Aprod 10 30
20 Diam A Aprod 20 40
20 Diam B Aprod 10 40
30 Dep 10 Aprod 10 *
30 Dep 20 Aprod 20 *
30 Dep 30 Aprod 30 *
20 Diam A Bprod 20 40
30 Dep 10 Bprod 10 *
我使用表中的行作为选择其他行的过滤器/掩码。
因此,用户已经选择了行:
PD Text
-------------
10 Aproducer
20 A
我现在想找出哪些 PD=30 的行适合之前的选择:
PD=10,Text=A Producer 给出 Mask_Producer 必须是“Aprod”,(Mask_Dep 和 Mask_Diam允许为任何星星)
PD=20,Text=A 给出 Mask_Producer 必须是“Aprod”,Mask_Dep 必须是 10 或 20,Mask_Diam 必须是 30 或 40(或星号)
我希望结果是第 6 行和上表中的 7 个。
然后想象一下这个例子有 2000 行和 20 个 Mask_xx 字段......
我正在考虑像 IN、LEFT JOIN、JOIN 和临时表这样的 SQL 来做到这一点,但我认为我可能让事情变得太复杂了......
Having this table (sorry, cant seem to get a decent layout here):
PD Header Text Mask_Producer Mask_Dep Mask_Diam
----------------------------------------------------------------------------------------------
10 Producer Aproducer Aprod * *
10 Producer Bproducer Bprod * *
20 Diam A Aprod 10 30
20 Diam A Aprod 20 40
20 Diam B Aprod 10 40
30 Dep 10 Aprod 10 *
30 Dep 20 Aprod 20 *
30 Dep 30 Aprod 30 *
20 Diam A Bprod 20 40
30 Dep 10 Bprod 10 *
I am using the rows in the table as a filter/mask for selecting other rows.
So, user having already made a selection of rows with:
PD Text
-------------
10 Aproducer
20 A
I would now like to find out what rows with PD=30 fits those previous choices:
PD=10, Text=Aproducer gives that Mask_Producer must be "Aprod", (Mask_Dep and Mask_Diam are allowed to be anything by the stars)
PD=20, Text=A gives that Mask_Producer must be "Aprod" and Mask_Dep must be 10 or 20 and Mask_Diam must be 30 or 40 (or star)
I want the outcome to be rows 6 and 7 from the table above.
Then imagine this example with 2000rows and 20 Mask_xx fields....
I am thinking SQL like IN, LEFT JOIN, JOIN, and temporary tables to do this, but I think I may be complicating things too much....
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我想我明白你的要求,但我更熟悉 SQL Server,所以如果我的语法有点不对,请原谅我。您应该能够进行内部联接,将表与其自身联接起来以获得您想要的结果。
看着这个我觉得我有点不对劲,但你的一些数据不清楚。您能否为您在 Mask_Dep 和 Mask_Diam 中寻找的内容提供更多通用规则?
I think I understand what you are asking for but I am more familiar with SQL Server so if my syntax is off a little please forgive me. You should be able to do an inner join, joining the table to itself to get the results you want.
Looking at this I think I am a bit off but some of your data isn't clear. Could you give more universal rules for what you are looking for in the Mask_Dep and Mask_Diam?
我认为你想要这样的东西:
这将采取选择的结果:
并将其自连接到主表,其中这些条件成立
r.mas_dep = '*' OR c.mask_dep = r.mask_dep
和r.mask_diam = '*' 或 c.mask_diam = r.mask_diam
。这将返回一组:它将明确排除其他行,因为它们的 mask_deps 为 30 不在选择结果集中,并且它将排除 pds 不是 30 的行。
I think you want something like this:
This will take the result of the choices:
And self-join it against the master table, where these conditions hold true
r.mas_dep = '*' OR c.mask_dep = r.mask_dep
andr.mask_diam = '*' OR c.mask_diam = r.mask_diam
. This will return a set of:It will exclude the others clearly because their mask_deps of 30 isn't in the choice resultset, and it will exclude the rows pds not 30.
这是我使用的代码类型,它有效,但是当我添加更多条件和字段时,我会得到更多巨大的连接,并且在每个连接中还有更多 AND..OR...OR 序列,因此欢迎任何改进建议!特别是我意识到这可能会像数据增长一样慢?也欢迎分析 sql 服务器对我的代码做什么!
现在我有大约 30 个条件/字段和一些为我生成 SQL 查询的 PHP 代码......
但这里是原理的较小示例:
This is the type of code I enede up using, it works, but as I add more criteria and fields I get more huge joins and in each join also more AND..OR...OR sequences, so any propositions for improvements are welcome! Especially I am consernde that this may be slow as data grovs? Analysis of what the sql sever has to do for my code is welcome too!
Right now I have about 30 criteria/fields and some PHP code that generates the SQL query for me....
But here is the smaller example for the principle: