怎样才能在我的表中获得每天的字数统计？

Question

我需要编写一个sql语句来获取表中每一天的WPM计数。它将有几天的多个条目；我只需要最高的WPM。另外，排除空条目。

The data set should look like:
Date_completed, Max_WPM
date here, wpm
date here, wpm

SELECT 
       DISTINCT A.DATE_COMPLETED
            ,A.WPM Max_WPM
FROM KEYBOARD_LEARNING A

我正在尝试过滤这个表的结果集中只返回不同日期的地方。但是上面的查询不起作用。如何使用WPM只返回不同的日期？

DATE_completed    MAX_WPM
--------------------------     ---------------
7/12/2012                ...
7/12/2012                ....
7/12/2012
7/22/2012
7/6/2012
7/12/2012
7/12/2012
7/6/2012
7/12/2012
7/12/2012
7/6/2012
7/6/2012
6/13/2012
7/12/2012
7/12/2012
7/16/2012
6/6/2012
7/6/2012
7/6/2012
6/12/2012
6/13/2012
7/6/2012
7/6/2012
7/6/2012
7/12/2012
6/13/2012
7/4/2012
6/14/2012
7/6/2012

Answer 1

听起来像是你想

SELECT A.DATE_COMPLETED
      ,MAX(A.WPM) Max_WPM
  FROM KEYBOARD_LEARNING A
 WHERE a.wpm IS NOT NULL
 GROUP BY a.date_completed

这假设所有的DATE_COMPLETED值都没有时间分量。如果是这样的话，你需要TRUNC一下日期

SELECT TRUNC(A.DATE_COMPLETED) date_completed
      ,MAX(A.WPM) Max_WPM
  FROM KEYBOARD_LEARNING A
 WHERE a.wpm IS NOT NULL
 GROUP BY TRUNC(a.date_completed)