这个SQL分组怎么写，求解！

fk1984316 2014-01-20 02:44:21

数据库表中的数据

列：用户分数时间
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

怎么得到每天最大时间的那条数据，得到的结果为：

列：用户分数时间
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

用一个SQL语句怎么处理，请指教！

...全文

336 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

AiDou_dream 2014-01-20

打赏
举报

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', 2, '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', 2, '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' ) --SELECT MAX(record),YEAR([date]) FROM @table GROUP BY YEAR([date]) SELECT [name] AS Name , MAX(record) AS MaxRecord , CAST([date] AS DATE) AS Date FROM @table GROUP BY Name , CAST([date] AS DATE) SELECT name , SUM(MaxRecord) AS SumRecord FROM ( SELECT [name] AS Name , MAX(record) AS MaxRecord , CAST([date] AS DATE) AS Date FROM @table GROUP BY Name , CAST([date] AS DATE) ) V GROUP BY Name

LongRui888 2014-01-20

打赏
举报

简化一下：


if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([用户] varchar(1),[分数] int,[时间] datetime)
insert [tb]
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  用户,SUM(分数) 分数
from
(
select *,
	   ROW_NUMBER() over(partition by 用户,convert(varchar(10),时间,120) 
							 order by 时间 desc) rownum
from tb
)t
where rownum = 1
group by 用户
/*
用户	分数
A	12
*/

LongRui888 2014-01-20

打赏
举报

简化一下：


if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([用户] varchar(1),[分数] int,[时间] datetime)
insert [tb]
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  用户,SUM(分数) 分数
from
(
select *,
	   ROW_NUMBER() over(partition by 用户,convert(varchar(10),时间,120) 
							 order by 时间 desc) rownum
from tb
)t
where rownum = 1
group by 用户
/*
用户	分数
A	12
*/

LongRui888 2014-01-20

打赏
举报

试试这个：

select  用户,SUM(分数) 分数
from
(
	select  用户,分数, 时间
	from
	(
	select *,
		   ROW_NUMBER() over(partition by 用户,convert(varchar(10),时间,120) 
								 order by 时间 desc) rownum
	from tb
	)t
	where rownum = 1
)t
group by 用户

發糞塗牆 2014-01-20

打赏
举报

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-20 14:58:03
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([用户] varchar(1),[分数] int,[时间] datetime)
insert [tb]
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'
--------------开始查询--------------------------
;WITH cte AS (
select[用户],[时间],[分数],ROW_NUMBER()OVER(PARTITION BY CONVERT(DATE,[时间]) ORDER BY [时间])  id
from [tb] )
SELECT  [用户],sum([分数])[分数]
FROM cte a
WHERE EXISTS (SELECT 1 FROM  (SELECT [用户], CONVERT(DATE,[时间])[时间],MAX(id)id FROM cte GROUP BY [用户], CONVERT(DATE,[时间]) )b WHERE a.id=b.id AND a.[用户]=b.[用户] AND CONVERT(DATE,a.[时间])=[时间])
GROUP BY [用户]


----------------结果----------------------------
/* 
用户   分数
---- -----------
A    12

*/

fk1984316 2014-01-20