为什么我要在非不同字段上进行内部联接？
我刚刚遇到一个在非不同字段上执行内部联接的查询。我以前从未见过这个，而且我对这个用法有点困惑。比如：
SELECT distinct all, my, stuff
FROM myTable
INNER JOIN myOtherTable
ON myTable.nonDistinctField = myOtherTable.nonDistinctField
(WHERE some filters here...)
我不太确定我的问题是什么或如何表达它，或者为什么这让我感到困惑，但我想知道是否有人可以解释为什么有人需要在非不同的字段上进行内部联接，并且然后只选择不同的值...？在非不同字段上是否合法使用内部联接？目的是什么？如果此类查询有正当理由，您能否举例说明在何处使用它？
I just came across a query that does an inner join on a non-distinct field. I've never seen this before and I'm a little confused about this usage. Something like:
SELECT distinct all, my, stuff
FROM myTable
INNER JOIN myOtherTable
ON myTable.nonDistinctField = myOtherTable.nonDistinctField
(WHERE some filters here...)
I'm not quite sure what my question is or how to phrase it, or why exactly this confuses me, but I was wondering if anyone could explain why someone would need to do an inner join on a non-distinct field and then select only distinct values...? Is there ever a legitimate use of an inner join on a non-distinct field? What would be the purpose? And if there's is a legitimate reason for such a query, can you give examples of where it would be used?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我似乎无法提出任何有效的理由来说明做你所要求的事情是有意义的。至少在系统受到极端限制的情况下是这样。
I can't seem to come up with any valid reasons where it would make sense to do what you're asking. At least not without extreme constraints on the system.
我能想到一个原因——非独特字段是外键。
该字段在外部表中是不同的，但在第一个表中不是。
例如，假设您正在清理一个旧的、重复的邮件列表，并且您已经修复了国家/地区字段，以便您存储国家/地区 ID，而不是存储国家/地区名称。您将countryid 连接到国家/地区以获取国家/地区数据，现在您可以在国家/地区表中存储其他数据。
现在，您可以获得国家/地区标准化的好处，但仍然具有与地址表不同的值。
有点做作，但会起作用。
I can think of a reason--the non-distinct field is a foreign key.
The field is distinct in the foreign table, but not in the first table.
For example, let's say you're cleaning up an old crufty duplicate-ridden mailing list and you have already fixed the country field so that instead of storing country name, you store country ID. You join on countryid to country to get the country data, and now you can store additional data in the country table.
You now get the normalization benefits of country but still distinct values from the address table.
Slightly contrived but will work.
一种可能可能的场景是针对特定时间段进行查询的基于日期的报告系统。举例来说，今年的前三个月。然后可以像您提到的那样连接另一个表中的相关数据，其中 nonDistinctField 是year.month(yyyy.mm)。尽管如此，连接解析可能没有多大意义，但您可以使用其他一些聚合函数（SUM、AVG 等）连接到分组月份。
我想应该有很多例子表明聚合查询可以从这种类型的连接中受益。
这并不是说这是一个好主意，但也许您可能仅限于使用非规范化数据或一些非常糟糕的数据模型。
One scenario that might be plausible would be a date based reporting system that was queried for a particular time period. Say for example, the first 3 months of the year. Then related data from another table could be joined like you've mentioned where nonDistinctField is the year.month(yyyy.mm). Still, the join resolution might not make a lot of sense but you could be using some other aggregate function (SUM, AVG etc.) joined to the grouped month.
I suppose there should be lots of examples where aggregate queries could benefit from this type of join.
Its not to say that thats a good idea but perhaps you might be limited to using denormalized data or some really bad data model.
我认为应该仅根据表之间的关系（具有不同的列）来连接表。当人们编写这样的查询时，可能应该重新审视数据库设计。
I think one should join tables only based on relations between them (with distinct columns). Probably one should have a second look at the database design when one is writing a query like that.