主页 / coding / 问题 / 77517652
Accepted
Zabs
Asked: 2023-11-21 00:53:11 +0800 CST

使用 NOT EXISTS() 时 MySQL 查询错误

  • 772

我正在尝试NOT EXISTS在旧系统（运行 MySQL 4.x）上利用 MySQL 函数运行 MySQL 查询，下面是一个简单的查询，它从两个表（带有一个 INNER JOIN）返回几列。

注意customerId和accountId是相同的值..这是一个非常旧的系统:(

SELECT d.customerId, d.customerName, a.status
    FROM DetailedDebtorsData AS d 
    INNER JOIN accounts AS a ON a.account_uid = d.customerId
    ORDER BY d.date ASC;

+---------------+----------------------+--------+
| customerId    | customerName         | status |
+---------------+----------------------+--------+
|        145060 | Alan Smith           | active |
|         68742 | John Doe             | active |
+---------------+----------------------+--------+

我有另一个表，我将用作“例外”表，本质上，如果在例外表中找到 customerId（及其排除项中的相关 account_id 列），那么我不想从DetailedDebtors 返回相关account_payment_terms行上表。

account_payment_terms表（用于排除）

mysql> SELECT account_id, created_date FROM account_payment_terms;
+------------+---------------------+
| account_id | created_date        |
+------------+---------------------+
|     145060 | 2023-11-20 13:23:03 |
+------------+---------------------+

由于 account_id145060存在于accountPaymentTerms表中，因此我不想返回DetailedDebtors要返回的表中的关联行（具有相同的 145060 id）。

这是我尝试使用 NOT EXISTS() 执行的查询

SELECT d.customerId, d.customerName, a.status
    FROM DetailedDebtorsData AS d 
    INNER JOIN accounts AS a ON a.account_uid = d.customerId
    WHERE NOT EXISTS (
        SELECT 1
        FROM account_payment_terms AS apt
        WHERE apt.account_id = d.customerId
    )        
    ORDER BY d.date ASC;

这个错误并给出了以下错误，但我希望 WHERE NOT EXISTS 仅返回一行，即不在表中的行account_payment_terms。

MySQL 返回错误

ERROR 1064 (HY000): You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (
        SELECT 1
        FROM account_payment_terms AS

预期结果（返回 1 行）

+---------------+----------------------+--------+
| accountId     | customerName         | status |
+---------------+----------------------+--------+
|         68742 | John Doe             | active |
+---------------+----------------------+--------+

实际结果（返回 2 行）

+---------------+----------------------+--------+
| accountId     | customerName         | status |
+---------------+----------------------+--------+
|        145060 | Alan Smith           | active |
|         68742 | John Doe             | active |
+---------------+----------------------+--------+

我究竟做错了什么？

sql

1 个回答

  1. Best Answer

    下面是在 MySQL 4.0 中执行排除连接的解决方案：

    SELECT d.customerId, d.customerName, a.status
FROM DetailedDebtorsData AS d 
INNER JOIN accounts AS a ON a.account_uid = d.customerId
LEFT OUTER JOIN account_payment_terms AS apt
  ON apt.account_id = d.customerId
WHERE apt.account_id IS NULL
ORDER BY d.date ASC;
    • 2

相关问题