----------------------------------------------------------------
-- 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*/
-- 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*/
解决方案 »
- 按月分类,进行汇总
- SQL问题
- 在线等...关于修改SQL SERVER端口号问题
- 这个查询怎么写
- SQL 2000 表变量和临时表的区别
- 请教几道题目,谢谢!
- WIN7 64位安装SQL SERVER 2008 R2 报错,,,请问如何解决?
- 数据库无法使用:Failed to reserve contiguous memory of Size= 65536
- sql server向其他DB迁移?
- weblogic6.1环境中操作sql server数据库出现的问题?!--详情请进:)
- SQL 规则查询 #################################### 100%结贴
- SQL2005中的权限查询问题
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 用户
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
*/
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
*/
(
[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