MySQL 查询建议请求
我可以就这个查询获得一些帮助吗？我会详细解释。为了方便起见，我将举一个包含 HTML 标签和属性的示例。
我有 3 个表：
- tbl1 - 包含所有 HTML 标记（
tagId,tagName） - tbl2 - 包含可能出现在 Tags (
attId,attName) - tbl3 - 是 tbl1 和 tbl2 的映射表 (
tagId,attId)
I想要选择属于所选标签（在下面的示例中标签 ID 4）和标签 ID 的所有属性（以及有关属性的相关信息）。
以下是我希望从查询中获得的内容的示例：
attId tagId attName
50 4 The name of the attribute with id 50
89 4 The name of the attribute with id 89
114 4 The name of the attribute with id 114
下面是我所做的查询，但我相信有更好的方法。
SELECT tbl2.*, tbl3.tagId
FROM tbl2 JOIN tbl3
WHERE tbl3.attId IN (
SELECT tbl3.attId FROM tbl3 where tbl3.tagId=4
)
AND tbl3.attIdd = tbl2.attId
GROUP BY tbl3.attId
提前致谢。
Can I get some help with this query. I will explain in details. To make it easier I'll take an example with HTML tags and attributes.
I have 3 table:
- tbl1 - contains all the HTML tags (
tagId,tagName) - tbl2 - contains all the available attributes that might appear in the
tags (attId,attName) - tbl3 - is a map table for tbl1 and tbl2 (
tagId,attId)
I want to select all the attributes (and related information about the attributes) that belong to the chosen tag (in the example below tag id 4) and the ID of the tag.
Here is an example of what I'd like to get from the query:
attId tagId attName
50 4 The name of the attribute with id 50
89 4 The name of the attribute with id 89
114 4 The name of the attribute with id 114
Below is the query that I've made, but I believe there is a better way.
SELECT tbl2.*, tbl3.tagId
FROM tbl2 JOIN tbl3
WHERE tbl3.attId IN (
SELECT tbl3.attId FROM tbl3 where tbl3.tagId=4
)
AND tbl3.attIdd = tbl2.attId
GROUP BY tbl3.attId
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
问题 1
您正在执行交叉联接，稍后通过在
where子句中放置过滤器将其简化为内部联接。这是使用隐式连接语法时的旧方法。
在大多数 SQL 方言（但不是 MySQL）上，这会产生错误。
切勿在没有 ON 子句的情况下进行连接。
如果要进行交叉联接，请使用以下语法：
select * from a cross join b问题 2
sub select 确实不需要，您可以在 where 子句中进行测试。
问题 3a
您正在
tbl3.ATTid上进行分组，在这种情况下，这与在tbl2.attid上进行分组相同（因为>ON (tbl3.attIdd = tbl2.attId)子句）后者更有意义，因为您正在执行
select tbl2.*，而不是select tbl3.*Issue 3b
如果 attId 不是 tbl2 的主键或唯一键，则查询结果将是不确定的。
这意味着查询将从具有相同
attID的可能行列表中随机选择一行。如果您不希望这样做，则必须包含一个having子句，该子句将根据某些条件选择一行。示例
Issue 1
You're doing cross join that you later reduce to an inner join by putting a filter in the
whereclause.This is the old-skool way when using implicit join syntax.
On most SQL-dialects (but not MySQL) this gives an error.
Never do a join without a ON clause.
If you want to do a cross join, use this syntax:
select * from a cross join bIssue 2
The sub select is really not needed, you can just do the test inside a where clause.
Issue 3a
You are doing a group by on
tbl3.ATTid, which in this context is the same as doing a group by ontbl2.attid(because of theON (tbl3.attIdd = tbl2.attId)clause)The latter makes a bit more sense, because you are doing
select tbl2.*, notselect tbl3.*Issue 3b
If attId is not a primary or unique key for tbl2, than the result of the query will be indeterminate.
That means that the query will select a row at random from a list of possible rows with the same
attID. If you don't want that you'll have to include ahavingclause that will choose a row based on some criterion.Example
你可以这样做。
You can do it like this instead.