简单的左连接记录加倍问题(Record

简单的左连接记录加倍问题(Record-doubling problem on a simple left join)

我正在运行这个查询：

CREATE TABLE SELECT people.*, Sheet1.department FROM people LEFT JOIN Sheet1 ON people.depno = Sheet1.depno

在一组详细说明员工记录的表格上。

目标是创建一个包含所有“人员”数据的新表格，以及一个可读的部门名称。 很简单，对吧？

问题是，结果表中的每条记录看起来都是完全重复的（字面上每个字段都是相同的），将大约23,000条记录表转换为大约46,000条记录表。 我说“粗略”，因为它不是完全翻倍 - 大约有一百个记录有差别。

一些细节：“人”表包含15个字段，包括“depno”字段，这是一个整数指示部门。

正如人们猜测的那样，“Sheet1”表是从导入的xls文件生成的，其中包含两个字段：共享的“depno”和新的“部门”（后者是与所讨论的depno对应的详细部门名称） 。 “Sheet1”表中有44条记录。

预先感谢任何关于此的指针。 让我知道你可以使用哪些其他信息。

---

更新：这是我最终使用的代码，从我对Johan的回应（再次感谢所有参与此工作的人）：

CREATE TABLE morebetter SELECT people.*, Sheet1.department FROM people LEFT JOIN Sheet1 ON people.depno = Sheet1.depno GROUP BY id

I'm running this query:

CREATE TABLE SELECT people.*, Sheet1.department FROM people LEFT JOIN Sheet1 ON people.depno = Sheet1.depno

On a set of tables detailing employee records.

The goal is to create a new table that has all the "people" data, plus a human-readable department name. Simple, right?

The problem is that each record in the resulting table appears to be duplicated exactly (with literally every field being the same), turning a roughly 23,000-record table into a roughly 46,000-record table. I say "roughly" because it's not an exact doubling -- there's a difference of about a hundred records.

Some details: The "people" table contains 15 fields, including the "depno" field, which is an integer indicating department.

The "Sheet1" table is, as one would guess, a table generated from an imported xls file containing two fields: the shared "depno" and a new "department" (the latter being a verbose department name corresponding to the depno in question). There are 44 records in the "Sheet1" table.

Thanks in advance for any pointers on this. Let me know what other information you can use from me.

---

Update: Here's the code I ended up using, from my response to Johan (thanks again to everyone who worked on this):

CREATE TABLE morebetter SELECT people.*, Sheet1.department FROM people LEFT JOIN Sheet1 ON people.depno = Sheet1.depno GROUP BY id

最满意答案

people.depno不是独一无二的，这就是为什么你要加倍。

将SELECT部分更改为

SELECT DISTINCT people.*, Sheet1.department FROM people LEFT JOIN Sheet1 ON people.depno = Sheet1.depno

这将消除重复的行。

在MySQL中，你也可以编写

SELECT people.*, Sheet1.department FROM people LEFT JOIN Sheet1 ON people.depno = Sheet1.depno GROUP BY people.depno

其中的作品略有不同。 第一个查询消除了重复输出的行，第二个查询消除了具有重复的people.depno记录，即使people.depno没有出现在输出中。

我喜欢第二种形式，因为它明确了你想要消除的重复内容，而且你不需要调整输出。 执行时间也稍微快一点。

***警告*** group by版本group by将消除它找到的任何double people.depno，但是如果select中的其他字段不相同，它将随机选择一个！ 换一种说法。 如果select distinct的结果与version group by groups不同，那意味着MySQL正在悄悄地删除非重复的行。 这可能是也可能不是你想要的！

为了安全起见，在你关心的所有领域做一个group by ！ 如果group by位于unique键上，而不包含与该唯一键相同的表中的更多字段。

The people.depno is not unique, that's why you're getting the doubling.

Change the SELECT part to

SELECT DISTINCT people.*, Sheet1.department FROM people LEFT JOIN Sheet1 ON people.depno = Sheet1.depno

This will eliminate duplicate rows.

In MySQL you can also write

SELECT people.*, Sheet1.department FROM people LEFT JOIN Sheet1 ON people.depno = Sheet1.depno GROUP BY people.depno

Which works slightly different. The first query eliminates rows with duplicate output, the second query eliminates records with duplicate people.depno, even if people.depno does not appear in the output.

I like the second form, because it makes explicit which duplicate you're trying to eliminate and you don't need to tweak the output. It's also slightly faster in executing time.

***Warning*** The group by version will eliminate any double people.depno it finds, but if the other fields in the select are not identical it will just choose one at random! In other words. If the outcome of the select distinct is different from the group by version that means that MySQL is silently dropping non-duplicate rows. This may or may not be what you want!

In order to be safe, do a group by on all fields that you care about! If the group by is on a unique key than it's pointless to include further fields from the same table as that unique key.