MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test (
id INT, login varchar(32), subs_id INT, ip VARCHAR(15), login_out BIGINT
);
SELECT @@SQL_MODE;


@@SQL_MODE


ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


explain
SELECT subs_id 
FROM test 
where login_out > 0 and (UNIX_TIMESTAMP() - login_out) < 30
GROUP BY subs_id 
HAVING COUNT(id) > 1
and login_out > 0
Unknown column 'login_out' in 'having clause'

explain
SELECT login, subs_id, ip, login_out 
FROM test 
WHERE subs_id IN (SELECT subs_id 
                  FROM test 
                  where login_out > 0 and (UNIX_TIMESTAMP() - login_out) < 30
                  GROUP BY subs_id 
                  HAVING COUNT(id) > 1
                  and login_out > 0
                  )
order by login;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
test
null
ALL
null
null
null
null
1
100.00
Using where; Using filesort

2
DEPENDENT SUBQUERY
test
null
ALL
null
null
null
null
1
100.00
Using where; Using temporary; Using filesort


explain
SELECT login, subs_id, ip, login_out 
FROM test t1
WHERE subs_id IN (SELECT subs_id 
                  FROM test t2
                  where login_out > 0 and (UNIX_TIMESTAMP() - login_out) < 30
                  GROUP BY subs_id 
                  HAVING COUNT(id) > 1
                  and t2.login_out > 0
                  )
order by login;
Unknown column 't2.login_out' in 'having clause'

explain
SELECT login, subs_id, ip, login_out 
FROM test t1
WHERE subs_id IN (SELECT subs_id 
                  FROM test t2
                  where login_out > 0 and (UNIX_TIMESTAMP() - login_out) < 30
                  GROUP BY subs_id 
                  HAVING COUNT(id) > 1
                  and t1.login_out > 0
                  )
order by login;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
t1
null
ALL
null
null
null
null
1
100.00
Using where; Using filesort

2
DEPENDENT SUBQUERY
t2
null
ALL
null
null
null
null
1
100.00
Using where; Using temporary; Using filesort

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	test	null	ALL	null	null	null	null	1	100.00	Using where; Using filesort
2	DEPENDENT SUBQUERY	test	null	ALL	null	null	null	null	1	100.00	Using where; Using temporary; Using filesort