432
432
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
433
433
id select_type table type possible_keys key key_len ref rows Extra
434
434
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
435
2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 t2.oref,func 4 Using where; Using index; Full scan on NULL key
435
2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 test.t2.oref,func 4 Using where; Using index; Full scan on NULL key
436
436
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
458
458
id select_type table type possible_keys key key_len ref rows Extra
459
459
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
460
2 DEPENDENT SUBQUERY t1 ref idx idx 5 t2.oref 2 Using where; Using temporary; Using filesort
460
2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort
462
462
a in (select min(ie) from t1 where oref=t2.oref
463
463
group by grp having min(ie) > 1) Z
647
647
drop table t1,t2;
648
CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
649
INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
650
(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'),
652
CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
653
INSERT INTO t2 SELECT * FROM t1;
654
SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b))
655
as test FROM t1 GROUP BY a;
660
SELECT * FROM t1 GROUP by t1.a
661
HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c
662
HAVING MAX(t2.b+t1.a) < 10));
664
SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c;
673
(SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)
676
(SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1)
678
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1)
680
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1)
683
a MAX(b) cnt t_b t_b t_b
688
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test