即使右表与位置不匹配，SQL 连接也会显示左表

Question

我有 2 个表，定义如下：

CREATE TABLE `product` (
`pid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR( 50 ) NOT NULL,
`description` TEXT,
`qty` SMALLINT( 5 ) UNSIGNED NOT NULL DEFAULT '0',
`category` ENUM( '1', '2', '3', '4', '5', '6', '7', '8' ) NOT NULL DEFAULT '1',
`price` DECIMAL( 7, 2 ) UNSIGNED NOT NULL
) ENGINE = InnoDB;

CREATE TABLE `discount` (
`did` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`pid` SMALLINT( 5 ) UNSIGNED NOT NULL,
`sDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`eDate` DATETIME NOT NULL,
`dPrice` DECIMAL( 7, 2 ) UNSIGNED NOT NULL,
FOREIGN KEY ( `pid` ) REFERENCES `product`(`pid`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;

我试图为每个产品获取恰好 1 行的结果，以及 NULL dPrice 或 dPrice（如果存在 sDate sDate sDate sDate < ）的折扣条目。 NOW() 和 eDate >现在（）。

我尝试过：

select p.pid, name, price, dPrice, qty
from product p left join discount d
on p.pid = d.pid
where d.sDate<now() and d.eDate>now();

问题是它只返回具有有效折扣的产品。没有折扣或过期/未来折扣的产品不会显示。

接下来我尝试：

select p.pid, name, price, dPrice, qty
from product p left join discount d
on p.pid = d.pid
where (d.sDate<now() and d.eDate>now()) or dPrice is null;

这离我想要的结果更近了一步，它列出了具有有效折扣的产品和没有折扣的产品，但我仍然缺少定义了过期/未来折扣的产品。

检查是否在任何时候只有 1 个折扣处于活动状态是在 PHP 中完成的，不需要包含在此语句中。非常感谢任何帮助！

Answer 1

select p.pid, name, price, dPrice, qty
from product p 

left join discount d on p.pid = d.pid and d.sDate<now() and d.eDate>now()

比子查询更高效、更“标准”。

Answer 2

怎么样

SELECT p.name, d.dPrice
FROM   product p LEFT JOIN discount d
ON     p.pid = d.pid AND now() BETWEEN d.sDate AND d.eDate

Answer 3

您可能希望子查询返回 discount 的过滤版本，然后您可以将其与 product 左连接。

select p.pid, name, price, dPrice, qty
from product p left join
(select * from discount where sDate<now() and eDate>now()) d
on p.pid = d.pid;

（这里的 SQL 语法可能有一个轻微的错误，但您明白了：由于您只想将 WHERE 子句应用于一个表，因此您可以在子查询中将其应用于该表，然后连接结果数据集，而不是先连接表然后过滤。）

Answer 4

这正是 外连接 的发明目的。

从产品 p 中选择 p.*,d.dPrice 在 p.pid=d.pid 上左外连接折扣 d 并在 d.sDate 和 d.eDate 之间使用 now()

您可以使用缩写 左连接而不是完整的左外连接。然而，最好记住它们是外连接而不是通常的内连接。