时间戳

首页 > 解决方案 > MySQL左连接中的语法问题

mysql - MySQL左连接中的语法问题

问题描述

在 mysql 的手册（https://dev.mysql.com/doc/refman/8.0/en/join.html）中有一个例子：

SELECT t1.name, t2.salary
FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

现在，这是我的情况。让我创建一个表和一些数据......

CREATE TABLE `tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `name` text ,
  `year` int(2) ,
  `num` int(5) ,
  PRIMARY KEY (`id`)
);

INSERT INTO tb (id, name,year,num) VALUES 
('1', 'a','19', 500),
('2', 'a','18', 400),
('3', 'b','19', 400),
('4', 'b','18', 200),
('5', 'c','19', 400),
('6', 'c','18', 100);

这是我的测试数据。

+----+------+------+------+
| id | name | year | num  |
+----+------+------+------+
|  1 | a    |   19 |  500 |
|  2 | a    |   18 |  400 |
|  3 | b    |   19 |  400 |
|  4 | b    |   18 |  200 |
|  5 | c    |   19 |  400 |
|  6 | c    |   18 |  100 |
+----+------+------+------+

我想得到一个左连接，左边是19的数据，右边是18的数据，如下：

+------+------+------+------+
| name | num  | name | num  |
+------+------+------+------+
| a    |  500 | a    |  400 |
| b    |  400 | b    |  200 |
| c    |  400 | c    |  100 |
+------+------+------+------+

一种右左连接如下：

SELECT * FROM
(
    SELECT `name`, `num`
    FROM `tb`
    WHERE `year` = 19
) AS a
LEFT JOIN
(
    SELECT `name`, `num`
    FROM `tb`
    WHERE `year` = 18
) AS b ON a.`name` = b.`name`;

我想知道为什么下面不能得到正确的结果？

SELECT `name`, `num`
FROM `tb`
WHERE `year` = 19
AS a
LEFT JOIN
(
    SELECT `name`, `num`
    FROM `tb`
    WHERE `year` = 18
) AS b ON a.`name` = b.`name`;

标签： mysqljoinleft-join

解决方案

您的最后一个查询有语法错误。WHERE 子句必须放在最后，您需要消除 SELECT 子句中列名的歧义：

SELECT a.`name`, a.`num`, b.`num`
FROM `tb` AS a
LEFT JOIN (
    SELECT `name`, `num`
    FROM `tb`
    WHERE `year` = 18
) AS b ON a.`name` = b.`name`
WHERE `year` = 19;

话虽如此，您也可以像这样编写查询：

SELECT l.name, l.year, l.num, r.year, r.num
FROM tb AS l
LEFT JOIN tb AS r ON l.name = r.name AND r.year = 18
WHERE l.year = 19

推荐阅读