Re: Re: A odd case about mysql sub query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



在 2016/5/12 7:28, haiwen zhu 写道:
On Wed, May 11, 2016 at 12:08 PM, soul11201 <1479418380@xxxxxx> 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


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




[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux