LIMIT＆IN / ALL / ANY / SOME子查询出现问题

Question

我有这个查询：

SELECT count(cp.CxID) as intSmokers 
FROM CustPrimarySmoking cp 
JOIN Customer c ON cp.CxID = c.CustomerID 
WHERE 
cp.CxID IN (SELECT CxID FROM CustPrimarySmoking WHERE CxID = cp.CxID LIMIT 1, 9999)

想法是，计数将基于嵌套查询的结果，该查询将检索除第一个记录以外的该客户的所有记录。

但是，我遇到了这个错误，我认为这是很严重的错误：

1235-此版本的MySQL尚不支持“ LIMIT＆IN / ALL / ANY / SOME子查询”

有人知道这样做的其他方式吗？

谢谢

Answer 1

这就是您需要进行的操作。请参阅我已经解决的示例。

mysql> select * from test;
+------+-------+
| id   | name  |
+------+-------+
|    1 | name1 |
|    2 | name2 |
|    3 | name3 |
|    4 | name4 |
+------+-------+
4 rows in set (0.00 sec)

mysql> select * from test1;
+------+------+--------+
| id   | tid  | name2  |
+------+------+--------+
|    1 |    2 | name11 |
|    2 |    3 | name12 |
|    3 |    4 | name13 |
+------+------+--------+
3 rows in set (0.00 sec)

mysql> select
    ->  t1.name
    -> from
    ->  test t1
    -> join
    ->  test1 t2 on t2.tid = t1.id
    -> join
    ->  (select id from test where id <4 limit 3) as tempt on tempt.id = t1.id;
+-------+
| name  |
+-------+
| name2 |
| name3 |
+-------+
2 rows in set (0.00 sec)

希望这可以帮助。