代码之家 › 专栏 › 技术社区 › luciferchase

如何连接两个表,使两个表中的同一列不重复?[副本]

mysql

-1

luciferchase · 技术社区 · 4 年前

我有两张桌子

Table 1: employee
+----+---------+--------+--------+------+-------+------+----------+
| No | Name    | Salary | Zone   | Age  | Grade | Dept | HireDate |
+----+---------+--------+--------+------+-------+------+----------+
|  1 | Mukul   |  30000 | West   |   28 | A     |   10 | NULL     |
|  2 | Kritika |  35000 | Centre |   30 | A     |   10 | NULL     |
|  3 | Naveen  |  35200 | West   |   40 | B     |   20 | NULL     |
|  4 | Uday    |  41800 | North  |   38 | C     |   30 | NULL     |
|  5 | Nupur   |  32000 | East   |   26 | B     |   20 | NULL     |
|  6 | Moksh   |  37000 | South  |   28 | B     |   10 | NULL     |
|  7 | Shelly  |  36000 | North  |   26 | A     |   30 | NULL     |
+----+---------+--------+--------+------+-------+------+----------+

Table 2:department
+------+---------+--------+--------+------+
| Dept | Dname   | Minsal | Maxsal | HoD  |
+------+---------+--------+--------+------+
|   10 | Sales   |  25000 |  32000 |    1 |
|   20 | Finance |  30000 |  50000 |    5 |
|   30 | Admin   |  25000 |  40000 |    7 |
+------+---------+--------+--------+------+

假设我想显示在销售部门工作的所有员工的详细信息。
我试过了 INNER JOIN

SELECT *
FROM employee AS A
INNER JOIN department AS B
ON A.Dept = B.Dept AND B.Dname = "Sales";

显示此表

+----+---------+--------+--------+------+-------+------+----------+------+-------+--------+--------+------+
| No | Name    | Salary | Zone   | Age  | Grade | Dept | HireDate | Dept | Dname | Minsal | Maxsal | HoD  |
+----+---------+--------+--------+------+-------+------+----------+------+-------+--------+--------+------+
|  1 | Mukul   |  30000 | West   |   28 | A     |   10 | NULL     |   10 | Sales |  25000 |  32000 |    1 |
|  2 | Kritika |  35000 | Centre |   30 | A     |   10 | NULL     |   10 | Sales |  25000 |  32000 |    1 |
|  6 | Moksh   |  37000 | South  |   28 | B     |   10 | NULL     |   10 | Sales |  25000 |  32000 |    1 |
+----+---------+--------+--------+------+-------+------+----------+------+-------+--------+--------+------+

如您所见,Dept列显示两次。

1如何只显示一次同名列?
2而不是 内部连接 如何才能做到这一点,即有什么更好的方法来做到这一点?

3 回复 | 直到 4 年前

Marek Puchalski 4 年前

这样地:

select A.No, A.Name, A.Salary, A.Zone, A.Age, A.Grade, A.Dept, A.HireDate,
   B.Dname, B.Minsal, B.Maxsal, B.HoD
(...)

内部连接是正确的连接方式。这边没问题。

juergen d 4 年前

* 您需要指定所需的列。您还可以限制 * 到单个表的所有列。例子:

SELECT e.*, d.Dname, d.HoD  
FROM employee AS e
INNER JOIN department AS d ON e.Dept = d.Dept 
WHERE d.Dname = "Sales";