mysql - 如何在 Sql View 查询中引用值

Question

--
-- Table structure for table `emp`
--

CREATE TABLE `emp` (
  `Emp_id` int(11) NOT NULL,
  `Name` varchar(30) DEFAULT NULL,
  `Sup_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `emp`
--

INSERT INTO `emp` (`Emp_id`, `Name`, `Sup_id`) VALUES
(1, 'Mark 18', NULL),
(2, 'Iron Man', 1),
(3, 'Hulk', NULL),
(4, 'Ant-Man', 2);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `emp`
--
ALTER TABLE `emp`
  ADD PRIMARY KEY (`Emp_id`),
  ADD KEY `Sup_id` (`Sup_id`);

--

这是我的表格 DESC

我想要这样的输出

就像钢铁侠一样我想要这样的输出

2钢铁侠马克18

这意味着它要检查Emp_id和Dispaly对应的Emp_id名称

DESc 结构

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| Emp_id | int(11)     | NO   | PRI | NULL    |       |
| Name   | varchar(30) | YES  |     | NULL    |       |
| Sup_id | int(11)     | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+

表内的值

+--------+----------+--------+
| Emp_id | Name     | Sup_id |
+--------+----------+--------+
|      1 | Mark 18  |   NULL |
|      2 | Iron Man |      1 |
|      3 | Hulk     |   NULL |
|      4 | Ant-Man  |      2 |
+--------+----------+--------+

我想要的输出格式

+--------+----------+--------+
| Emp_id | Name     | Sup_id |
+--------+----------+--------+
|      1 | Mark 18  |NULL    |
|      2 | Iron Man |MArk 18 |
|      3 | Hulk     |   NULL |
|      4 | Ant-Man  |Iron Man|
+--------+----------+--------+

Answer 1

只需 LEFT JOIN 表本身:

SELECT e.Emp_id, e.Name, es.Name AS Sup_name FROM emp AS e LEFT JOIN emp AS es ON e.Sup_id = es.Emp_id