我自建了一张测试表,搜索出来就是这样的! mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.20 | +-----------+ 1 row in set (0.00 sec)mysql> select id from test; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | +----+ 8 rows in set (0.00 sec)mysql> select id from test group by `field`; +----+ | id | +----+ | 1 | | 5 | | 3 | | 4 | | 2 | +----+ 5 rows in set (0.00 sec)mysql> select id from test where id in(select id from test group by `field`); +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | +----+ 8 rows in set (0.01 sec) 另外一台版本5.6.21也是一样的结果; 换一台版本为5.1.73的 mysql> select version(); +-----------+ | version() | +-----------+ | 5.1.73 | +-----------+ 1 row in set (0.00 sec)mysql> select id from test; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | +----+ 8 rows in set (0.00 sec)mysql> select id from test group by `field`; +----+ | id | +----+ | 1 | | 5 | | 3 | | 4 | | 2 | +----+ 5 rows in set (0.00 sec)mysql> select id from test where id in(select id from test group by `field`); +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+ 5 rows in set (0.00 sec)
没有这种问题 select id from test where id in(select id from test group by `field`); +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+ 5 rows in set (0.01 sec)
我版本 5.1.73也没这个问题 但是其他5.6.21和5.6.20都出现了这个问题 郁闷了好久 么用过那么搞的版本,你去MYSQL官方论坛看看吧 我同事写这样结果就和之前的一样了 select * from `test` where `id` in (select * from (select `id` from `test` group by `field`)as `ttt` );
这两个查询结果是一样的,否则不一样
我自建了一张测试表,搜索出来就是这样的!
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.20 |
+-----------+
1 row in set (0.00 sec)mysql> select id from test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+
8 rows in set (0.00 sec)mysql> select id from test group by `field`;
+----+
| id |
+----+
| 1 |
| 5 |
| 3 |
| 4 |
| 2 |
+----+
5 rows in set (0.00 sec)mysql> select id from test where id in(select id from test group by `field`);
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+
8 rows in set (0.01 sec)
另外一台版本5.6.21也是一样的结果;
换一台版本为5.1.73的
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73 |
+-----------+
1 row in set (0.00 sec)mysql> select id from test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+
8 rows in set (0.00 sec)mysql> select id from test group by `field`;
+----+
| id |
+----+
| 1 |
| 5 |
| 3 |
| 4 |
| 2 |
+----+
5 rows in set (0.00 sec)mysql> select id from test where id in(select id from test group by `field`);
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.00 sec)
所以,第一个查询画蛇添足了你看下5#的数据怎么会这样
你field的数据呢?
所以,第一个查询画蛇添足了你看下5#的数据怎么会这样
你field的数据呢?不对,细看了下,这两个查询结果是一样的,你ID是递增的,不会重复
所以,第一个查询画蛇添足了你看下5#的数据怎么会这样
你field的数据呢?
整个表数据ru'x
mysql> select * from test;
+----+---------+
| id | field |
+----+---------+
| 1 | 123123 |
| 2 | 653456 |
| 3 | 34654 |
| 4 | 46731 |
| 5 | 1523545 |
| 6 | 123123 |
| 7 | 123123 |
| 8 | 653456 |
+----+---------+
8 rows in set (0.00 sec)不对,细看了下,这两个查询结果是一样的,你ID是递增的,不会重复
所以,第一个查询画蛇添足了你看下5#的数据怎么会这样
你field的数据呢?
mysql> select * from test;
+----+---------+
| id | field |
+----+---------+
| 1 | 123123 |
| 2 | 653456 |
| 3 | 34654 |
| 4 | 46731 |
| 5 | 1523545 |
| 6 | 123123 |
| 7 | 123123 |
| 8 | 653456 |
+----+---------+
8 rows in set (0.00 sec)数据有这些
select id from test where id in(select id from test group by `field`);
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.01 sec)
但是其他5.6.21和5.6.20都出现了这个问题
郁闷了好久
么用过那么搞的版本,你去MYSQL官方论坛看看吧
但是其他5.6.21和5.6.20都出现了这个问题
郁闷了好久
么用过那么搞的版本,你去MYSQL官方论坛看看吧
我同事写这样结果就和之前的一样了
select * from `test` where `id` in (select * from (select `id` from `test` group by `field`)as `ttt` );