mysql group by 最小值_在mysql中选择group by的最小值

我的数据库方案,

CREATE TABLE `result` (

`ID` bigint(21) NOT NULL AUTO_INCREMENT,

`datetime` datetime DEFAULT NULL,

`recordnum` int(11) DEFAULT NULL,

`recordtype` int(11) DEFAULT NULL,

`minvalue` int(11) DEFAULT NULL,

`maxvalue` int(11) DEFAULT NULL,

`data1` int(11) DEFAULT NULL,

`data2` int(11) DEFAULT NULL,

`area` varchar(32) DEFAULT NULL,

PRIMARY KEY (`ID`),

UNIQUE KEY `res` (`datetime`,`recordnum`,`area`)

) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

数据样本

06001

我想要的是在每个记录号和区域以及日期的最小值最小值时查询每个值

+----+---------------------+-----------+-------+----------+----------+-------+-------+------------+

| ID | datetime | recordnum | area |minvalue | maxvalue | data1 | data2 | recordtype |

+----+---------------------+-----------+-------+----------+----------+-------+-------+------------+

| 2 | 2013-03-26 12:03:31 | 2 | Zone2 | 2 | 7 | 20 | 20 | 1 |

| 3 | 2013-03-26 12:03:31 | 5 | Zone2 | 1 | 2 | 8 | 15 | 2 |

| 4 | 2013-03-26 12:00:31 | 2 | Zone1 | 2 | 7 | 100 | 200 | 1 |

| 8 | 2013-03-25 12:03:31 | 2 | Zone2 | 2 | 7 | 20 | 20 | 1 |

| 9 | 2013-03-25 12:03:31 | 5 | Zone2 | 1 | 2 | 8 | 15 | 2 |

| 16 | 2013-03-25 11:00:31 | 2 | Zone1 | 1 | 7 | 100 | 200 | 1 |

+----+---------------------+-----------+-------+----------+----------+-------+-------+------------+

这个查询给出了很好的结果,但仍然显示重复记录,请帮忙

SELECT a.*

FROM result a

JOIN

(

SELECT datetime as mindatetime,DATE(datetime) dateOnly,area, recordNum, MIN(minvalue) min_val

FROM result

GROUP BY dateOnly,area, recordNum

) b ON a.area = b.area AND

a.recordNUM = b.recordNum AND

a.minvalue = b.min_val ;

+----+---------------------+-----------+------------+----------+----------+-------+-------+-------+

| ID | datetime | recordnum | recordtype | minvalue | maxvalue | data1 | data2 | area |

+----+---------------------+-----------+------------+----------+----------+-------+-------+-------+

| 2 | 2013-03-26 12:03:31 | 2 | 1 | 2 | 7 | 20 | 20 | Zone2 |

| 2 | 2013-03-26 12:03:31 | 2 | 1 | 2 | 7 | 20 | 20 | Zone2 |

| 3 | 2013-03-26 12:03:31 | 5 | 2 | 1 | 2 | 8 | 15 | Zone2 |

| 3 | 2013-03-26 12:03:31 | 5 | 2 | 1 | 2 | 8 | 15 | Zone2 |

| 4 | 2013-03-26 12:00:31 | 2 | 1 | 2 | 7 | 100 | 200 | Zone1 |

| 8 | 2013-03-25 12:03:31 | 2 | 1 | 2 | 7 | 20 | 20 | Zone2 |

| 8 | 2013-03-25 12:03:31 | 2 | 1 | 2 | 7 | 20 | 20 | Zone2 |

| 9 | 2013-03-25 12:03:31 | 5 | 2 | 1 | 2 | 8 | 15 | Zone2 |

| 9 | 2013-03-25 12:03:31 | 5 | 2 | 1 | 2 | 8 | 15 | Zone2 |

| 10 | 2013-03-25 12:00:31 | 2 | 1 | 2 | 7 | 100 | 200 | Zone1 |

| 16 | 2013-03-25 11:00:31 | 2 | 1 | 1 | 7 | 100 | 200 | Zone1 |

+----+---------------------+-----------+------------+----------+----------+-------+-------+-------+