SQL_求集合中每天最大时间记录的总和

--问题求 集合中每天最大时间的总和

  表中的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
列: 用户     分数        时间
      A        2        2014-01-01 01:00:00  
      A        2        2014-01-01 02:00:00
      A        2        2014-01-01 03:00:00
      A        2        2014-01-02 01:00:00
      A        2        2014-01-02 02:00:00
      A        2        2014-01-02 03:00:00
      A        2        2014-01-03 02:00:00
      A        2        2014-01-03 03:00:00
      A        2        2014-01-04 01:00:00
      A        2        2014-01-05 01:00:00
      A        2        2014-01-06 01:00:00
      A        2        2014-01-06 02:00:00

 

  怎么得到每天最大时间的那条数据,最后的结果要为:  

1
2
3
4
5
6
7
列:  用户     分数       时间
      A        2        2014-01-01 03:00:00
      A        2        2014-01-02 03:00:00
      A        2        2014-01-03 03:00:00
      A        2        2014-01-04 01:00:00
      A        2        2014-01-05 01:00:00
      A        2        2014-01-06 02:00:00

  然后再对这个结果进行用户的分组,求分数的总和。
  得到的最终结果为:
  A  12

 

DECLARE @table TABLE
    (
      [id] INT PRIMARY KEY IDENTITY(1, 1) NOT NULL ,
      [name] VARCHAR(30) NOT NULL ,
      [record] INT NOT    NULL ,
      [date] DATETIME NOT    NULL
    )
INSERT  INTO @table
SELECT  A ,2 ,2014-01-01 01:00:00 UNION  ALL
SELECT  A ,2 ,2014-01-01 02:00:00 UNION  ALL
SELECT  A ,2 ,2014-01-01 03:00:00 UNION  ALL
SELECT  A ,2 ,2014-01-02 01:00:00 UNION  ALL
SELECT  A ,2 ,2014-01-02 02:00:00 UNION  ALL
SELECT  A ,2 ,2014-01-02 03:00:00 UNION  ALL
SELECT  A ,2 ,2014-01-03 02:00:00 UNION  ALL
SELECT  A ,2 ,2014-01-03 03:00:00 UNION  ALL
SELECT  A ,2 ,2014-01-04 01:00:00 UNION  ALL
SELECT  A ,2 ,2014-01-05 01:00:00 UNION  ALL
SELECT  A ,2 ,2014-01-06 01:00:00 UNION  ALL
SELECT  A ,2 ,2014-01-06 02:00:00

SELECT * FROM @table

   排序,为分组做准备,partition分区 插入行号,并按照时间排序

WITH    q AS ( SELECT   [name] ,[record] ,[date] ,
                        ROW_NUMBER() OVER ( PARTITION BY CAST(date AS DATE) ORDER BY [date] DESC ) AS rownum
               FROM     @table
             )
SELECT  * FROM    q

  取得一天中最大的记录

SELECT  * FROM    q WHERE rownum = 1

   求和

SELECT  name,SUM(record) AS totolrecord
FROM    q
GROUP BY rownum ,name
HAVING  rownum = 1

原题:http://bbs.csdn.net/topics/390697419

 

--我的理解题意错误解答,数据也私自更改了为的是更好的区分

 

DECLARE @table TABLE
    (
      [id] INT PRIMARY KEY
               IDENTITY(1, 1)
               NOT NULL ,
      [name] VARCHAR(30) NOT    NULL ,

      [record] INT NOT    NULL ,
      [date] DATETIME NOT    NULL
    )
INSERT  INTO @table( name, record, date )VALUES  ( A, 1, 2014-01-01 01:00:00 )
INSERT  INTO @table( name, record, date )VALUES  ( A, 2, 2014-01-01 02:00:00 )
INSERT  INTO @table( name, record, date )VALUES  ( A, 3, 2014-01-01 03:00:00 )
INSERT  INTO @table( name, record, date )VALUES  ( A, 2, 2014-01-02 01:00:00 )
INSERT  INTO @table( name, record, date )VALUES  ( A, 2, 2014-01-02 02:00:00 )
INSERT  INTO @table( name, record, date )VALUES  ( A, 2, 2014-01-02 03:00:00 )
INSERT  INTO @table( name, record, date )VALUES  ( A, 2, 2014-01-03 02:00:00 )
INSERT  INTO @table( name, record, date )VALUES  ( A, 2, 2014-01-03 03:00:00 )
INSERT  INTO @table( name, record, date )VALUES  ( A, 2, 2014-01-04 01:00:00 )
INSERT  INTO @table( name, record, date )VALUES  ( A, 2, 2014-01-05 01:00:00 )
INSERT  INTO @table( name, record, date )VALUES  ( A, 2, 2014-01-06 01:00:00 )
INSERT  INTO @table( name, record, date )VALUES  ( A, 2, 2014-01-06 02:00:00 )


按照分组,查询最大的record记录

SELECT MAX(record),YEAR([date]) FROM @table GROUP BY YEAR([date])

按照年月日分组,取每日中最大的record记录

SELECT  [name] AS Name ,MAX(record) AS MaxRecord ,CAST([date] AS DATE) AS Date
FROM    @table
GROUP BY Name ,CAST([date] AS DATE)

  

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。