SELECT CONVERT(varchar(20), QueryTime, 120) , SUM(Power) FROM PowerData
WHERE (DevAddr = '000000000152') AND (QueryTime BETWEEN '2008-08-01 00:00:00' AND '2009-08-01 00:00:00')
GROUP BY CONVERT(varchar(20), QueryTime, 120)在网上查了这么一个办法似乎没有,只想求每天的一个最大值,其他的都不要,这个语句应该怎么写啊?
WHERE (DevAddr = '000000000152') AND (QueryTime BETWEEN '2008-08-01 00:00:00' AND '2009-08-01 00:00:00')
GROUP BY CONVERT(varchar(20), QueryTime, 120)在网上查了这么一个办法似乎没有,只想求每天的一个最大值,其他的都不要,这个语句应该怎么写啊?
WHERE (DevAddr = '000000000152') AND (QueryTime BETWEEN '2008-08-01 00:00:00' AND '2009-08-01 00:00:00')
mysql> select * from powerdata;
+---------------------+
| querytime |
+---------------------+
| 2008-08-01 00:00:00 |
| 2008-08-03 20:30:41 |
| 2008-08-01 12:30:10 |
| 2008-08-02 23:10:54 |
| 2008-08-02 14:12:36 |
| 2008-08-03 14:54:18 |
| 2008-09-23 12:44:21 |
+---------------------+
7 rows in set (0.00 sec)
mysql> select max(querytime) mtime from powerdata where querytime between
-> '2008-08-01 00:00:00' and '2008-08-03 23:59:59' group by left(querytime,1
0);
+---------------------+
| mtime |
+---------------------+
| 2008-08-01 12:30:10 |
| 2008-08-02 23:10:54 |
| 2008-08-03 20:30:41 |
+---------------------+
3 rows in set (0.00 sec)看看这个语句能不能满足你的要求。
GROUP BY CONVERT(varchar(10), QueryTime, 120)