---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-03-24 09:44:07 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([username] varchar(5),[createtime] datetime,[type] varchar(7),[amount] int) insert [tb] select 'ssss1','2014-03-01','deposit',200 union all select 'ssss1','2014-03-01','deposit',300 union all select 'ssss1','2014-03-03','deposit',400 union all select 'ssss1','2014-03-03','deposit',500 union all select 'ssss1','2014-03-05','deposit',700 union all select 'ssss1','2014-03-06','deposit',1000 union all select 'ssss1','2014-03-07','deposit',2000 union all select 'ssss1','2014-03-08','deposit',100 union all select 'ssss1','2014-03-09','deposit',100 union all select 'ssss1','2014-03-10','deposit',500 union all select 'ssss1','2014-03-11','deposit',700 union all select 'ssss1','2014-03-12','deposit',800 --------------开始查询-------------------------- ;WITH f AS ( SELECT TOP 10 * FROM (SELECT ROW_NUMBER()OVER(PARTITION BY createtime ORDER BY createtime ) AS px,* FROM tb)t WHERE px=1 ORDER BY t.createtime DESC )SELECT TOP 1 username,createtime,type,amount FROM f WHERE px=1 ORDER BY amount DESC ----------------结果---------------------------- /* username createtime type amount -------- ----------------------- ------- ----------- ssss1 2014-03-07 00:00:00.000 deposit 2000(1 行受影响) */
declare @UserName varchar(50)='ssss1'; with t as ( select top 10 createtime,MAX(amount) amount from tb1 where username=@UserName and TYPE='deposit' group by createtime Order by createtime ) select top 1 @UserName UserName,* from t order by amount desc;
1. 数据表createtime 字段的值好像不是日期格式喔?
2.需要的查询,查询输入参数是什么?希望结果是什么?
3."查询的开始时间可以是这个用户的创建时间,结束时间可以是系统的当前时间", --> 请问用户的创建时间,结束时间从哪里得来?
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-24 09:44:07
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([username] varchar(5),[createtime] datetime,[type] varchar(7),[amount] int)
insert [tb]
select 'ssss1','2014-03-01','deposit',200 union all
select 'ssss1','2014-03-01','deposit',300 union all
select 'ssss1','2014-03-03','deposit',400 union all
select 'ssss1','2014-03-03','deposit',500 union all
select 'ssss1','2014-03-05','deposit',700 union all
select 'ssss1','2014-03-06','deposit',1000 union all
select 'ssss1','2014-03-07','deposit',2000 union all
select 'ssss1','2014-03-08','deposit',100 union all
select 'ssss1','2014-03-09','deposit',100 union all
select 'ssss1','2014-03-10','deposit',500 union all
select 'ssss1','2014-03-11','deposit',700 union all
select 'ssss1','2014-03-12','deposit',800
--------------开始查询--------------------------
;WITH f AS
(
SELECT TOP 10 * FROM (SELECT ROW_NUMBER()OVER(PARTITION BY createtime ORDER BY createtime ) AS px,* FROM tb)t WHERE px=1 ORDER BY t.createtime DESC
)SELECT TOP 1 username,createtime,type,amount FROM f WHERE px=1 ORDER BY amount DESC ----------------结果----------------------------
/* username createtime type amount
-------- ----------------------- ------- -----------
ssss1 2014-03-07 00:00:00.000 deposit 2000(1 行受影响)
*/
declare @UserName varchar(50)='ssss1';
with t as (
select top 10 createtime,MAX(amount) amount
from tb1
where username=@UserName and TYPE='deposit'
group by createtime
Order by createtime
)
select top 1 @UserName UserName,* from t order by amount desc;