Re: Re: A odd case about mysql sub query

在 2016/5/12 7:28, haiwen zhu 写道:

On Wed, May 11, 2016 at 12:08 PM, soul11201 <1479418380@qq.com> wrote:

在 2016/5/6 15:45, haiwen zhu 写道:

Hi guys, I have a table like this: +----+-------+-------+ | id | col_a | col_b | +----+-------+-------+

|  1 | a     | b     |
|  2 | a     | b     |
|  3 | a     | c     |
|  4 | a     | c     |

+----+-------+-------+ execute sql a: select * from test where id not in (select id from (select id from test

group by col_a,col_b) as a);

can get result +----+-------+-------+ | id | col_a | col_b | +----+-------+-------+

|  2 | a     | b     |
|  4 | a     | c     |

+----+-------+-------+ while execute sql b: select * from test where id not in (select id from (select * from test) as

a group by col_a,col_a);

get empty result. I'm very confused with this, somebody has idea about why? I tried in 10.1.13-MariaDB and mysql5.6.

in mysql 5.5.46, the second result I got was : +----+-------+-------+ | id | col_a | col_b | +----+-------+-------+

|  2 | a     | b     |
|  3 | a     | c     |
|  4 | a     | c     |

+----+-------+-------+ (:( -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

The second sql is：

select * from test where id not in (select id from (select * from test) as a group by col_a,col_b

My expected result is same as the first sql query.

select * from test where id not in (select id from (select * from test) as a group by col_a,col_b select * from test.new_table where id not in (select id from (select id from test.new_table group by col_a,col_b) as a); yes i got the same result in mysql 5.5.46