NOT功能不适用于我的mysql版本。

Question

我有一个在mysql版本5.5.44-0上工作的语句，但是它在mysql 4.0.24中根本不起作用。

有什么想法我可以改变使它向后兼容吗？

select distinct cust_id
from billing where billing.cust_id
not in (
select distinct billing_id from internet
);

这些是桌子

mysql> select * from billing;
+----+---------+-------+
| id | cust_id | cost  |
+----+---------+-------+
|  2 |      34 |   500 |
|  3 |      12 | 67700 |
|  4 |      99 |   100 |
|  5 |      99 |  1700 |
|  6 |    1450 |   800 |
|  7 |      88 |     0 |
|  8 |     222 |  5100 |
|  9 |     288 |  5100 |
| 10 |     329 |  5100 |
+----+---------+-------+


mysql> select * from internet;
+----+------------+------+
| id | billing_id | cost |
+----+------------+------+
|  1 |       1450 |  900 |
|  2 |         99 |  900 |
|  3 |        899 |  900 |
|  4 |        329 |  900 |
+----+------------+------+

在5.5.44-0版本

mysql> select distinct cust_id
    -> from billing where billing.cust_id
    -> not in (
    -> select distinct billing_id from internet
    -> );
+---------+
| cust_id |
+---------+
|      34 |
|      12 |
|      88 |
|     222 |
|     288 |
+---------+

在4.0.24版本

mysql> select distinct cust_id
    -> from billing where billing.cust_id
    -> not in (
    -> select distinct billing_id from internet
    -> );
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 'select distinct billing_id from internet
)' at line 4

Answer 1

哇，不幸的是，您必须使用MySQL 4.0，这是绝对古老的，不支持大多数类型的子查询或子选择。您可以将查询重写为LEFT JOIN，其中WHERE子句对NULL值进行筛选，以指示与NOT IN ()不匹配的值。

SELECT
  distinct cust_id
FROM
  billing
  -- Express the equivalence you matched with NOT IN() 
  -- as a left join ON condition
  LEFT JOIN internet ON billing.cust_id = internet.billing_id
WHERE
  -- A NULL value on the joined table indicates
  -- the relationship doesn't exist / there is no matching
  -- `billing_id` record in `internet` for the `cust_id`
  internet.billing_id IS NULL

如果您可以在该服务器上获得更新的MySQL版本，则强烈建议您使用该版本。4.0发行版可以追溯到2002年，也就是MySQL生命周期的早期，它将失去许多更重要的特性和安全性改进。