--问题求 集合中每天最大时间的总和
表中的数据
|
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)