SELECT A.*,B1.* FROM ( SELECT *,FLOOR(A/10) AS BZ FROM TMP A ) A , (SELECT BZ,AVG(C),AVG(D),MAX(C) FROM ( SELECT *,FLOOR(A/10) AS BZ FROM TMP A) A1 GROUP BY BZ) B1 WHERE NOT EXISTS(SELECT 1 FROM (SELECT *,FLOOR(A/10) AS BZ FROM TMP A ) B WHERE A.BZ=BZ AND A.C<B.C) AND A.BZ=B1.BZ可以将SELECT *,FLOOR(A/10) AS BZ FROM TMP A生成VIEW,再连接
mysql> select * from ( -> select a div 10 as k,avg(c),avg(d),max(c) as maxc -> from tmp -> group by a div 10 -> ) a inner join tmp b on a.k=b.a div 10 and a.maxc=b.c; +------+---------+---------+------+------+------+------+------+ | k | avg(c) | avg(d) | maxc | a | b | c | d | +------+---------+---------+------+------+------+------+------+ | 0 | 1.0000 | 1.0000 | 2 | 2 | 2 | 2 | 2 | | 1 | 11.0000 | 11.0000 | 12 | 13 | 2 | 12 | 12 | +------+---------+---------+------+------+------+------+------+ 2 rows in set (0.00 sec)mysql>
OR SELECT * FROM ( SELECT A DIV 10 AS BZ,AVG(C),AVG(D),MAX(C) AS MA FROM TMP GROUP BY A DIV 10) A1 INNER JOIN TMP B1 ON A1.MA=B1.C
SELECT *,FLOOR(A/10) AS BZ FROM TMP A ) A ,
(SELECT BZ,AVG(C),AVG(D),MAX(C) FROM (
SELECT *,FLOOR(A/10) AS BZ FROM TMP A) A1 GROUP BY BZ) B1
WHERE NOT EXISTS(SELECT 1 FROM
(SELECT *,FLOOR(A/10) AS BZ FROM TMP A ) B WHERE A.BZ=BZ AND A.C<B.C)
AND A.BZ=B1.BZ可以将SELECT *,FLOOR(A/10) AS BZ FROM TMP A生成VIEW,再连接
-> select a div 10 as k,avg(c),avg(d),max(c) as maxc
-> from tmp
-> group by a div 10
-> ) a inner join tmp b on a.k=b.a div 10 and a.maxc=b.c;
+------+---------+---------+------+------+------+------+------+
| k | avg(c) | avg(d) | maxc | a | b | c | d |
+------+---------+---------+------+------+------+------+------+
| 0 | 1.0000 | 1.0000 | 2 | 2 | 2 | 2 | 2 |
| 1 | 11.0000 | 11.0000 | 12 | 13 | 2 | 12 | 12 |
+------+---------+---------+------+------+------+------+------+
2 rows in set (0.00 sec)mysql>
SELECT * FROM (
SELECT A DIV 10 AS BZ,AVG(C),AVG(D),MAX(C) AS MA FROM TMP GROUP BY A DIV 10) A1 INNER JOIN TMP B1 ON A1.MA=B1.C