LIMIT&的问题IN/ALL/ANY/SOME子查询 [英] Problem with LIMIT & IN/ALL/ANY/SOME subquery

问题描述

我有这个查询:

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)

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

The idea being that the count will be based on the results of the nested query which retrieves all the records for that customer EXCEPT the first record.

但是，我遇到了这个错误，我认为这是一个相当大的错误:

HOWEVER, I get this error, which I think is pretty terminal:

1235-此版本的MySQL尚不支持'LIMIT& IN/ALL/ANY/SOME子查询"

1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

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

Does anyone know of any other way of doing this?

谢谢

推荐答案

这是您需要进行的步骤.请参阅我已经解决的示例.

This is how you need to proceed. See the example that I've worked out.

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)

希望这会有所帮助.

这篇关于LIMIT&的问题IN/ALL/ANY/SOME子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！