求最大值及日期，高手求助！

guosenwh 2010-09-27 03:30:28

brokerid fundid zc date
123 145 48988 20100401
123 356 48895 20100421
456 555 5897 20100526
456 444 578 20100621
789 888 693 20100722
789 988 5879 20100421

求指定月哪一天的客户经理（brokerid）有最大资产（zc）。fundid（客户编号），有临时表也可，谢谢！

...全文

77 点赞收藏 15

写回复

15 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

guosenwh 2010-09-27

不好意思，我再发个帖。

回复赞

njz168 2010-09-27



create table [TB]([brokerid] int,[fundid] int,[zc] int,[date] datetime)

insert [TB]

select 123,145,48988,'20100401' union all

select 123,356,48895,'20100421' union all

select 456,555,5897,'20100526' union all

select 456,444,578,'20100621' union all

select 789,888,693,'20100722' union all

select 789,988,5879,'20100421'

GO



create function fn_get_brokerid_maxzc(@bid int,@month int)

returns @t table(bid int,zc int,[date] datetime)

as

begin

   insert into @t

   select brokerid,sum(zc) ,max([date]) from TB

   where brokerid =@bid and convert(varchar(6),[date],112)) = @month

   group by brokerid

end

回复赞

王向飞 2010-09-27

把你想要的结果贴出来

回复赞

njz168 2010-09-27



create table [TB]([brokerid] int,[fundid] int,[zc] int,[date] datetime)

insert [TB]

select 123,145,48988,'20100401' union all

select 123,356,48895,'20100421' union all

select 456,555,5897,'20100526' union all

select 456,444,578,'20100621' union all

select 789,888,693,'20100722' union all

select 789,988,5879,'20100421'

GO



create function fn_get_brokerid_maxzc(@bid int,@month int)

returns @t table(bid int,zc int,[date] datetime)

as

begin

   insert into @t

   select brokerid,sum(zc) ,max([date]) from TB

   where brokerid =@bid and convert(varchar(6),[date],112)) = @month

end

回复赞

guosenwh 2010-09-27

这个合计是每一天的客户经理名下客户资产的合计，也就是每天客户经理名下客户总资产，一个月有30天的数据，求30天中资产最大的值以及是这个最大值是哪一天产生的。

回复赞

dawugui 2010-09-27

最好给出完整的表结构,测试数据,计算方法和正确结果.

发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

回复赞

王向飞 2010-09-27

怎么可能呢。。你不是要求合计吗？合计怎么算到哪一天？是一个月的合计啊？

回复赞

guosenwh 2010-09-27

事实上这个表记录不够，每天的客户资产记录都在1万多条，客户有对应的客户经理，我需要知道这个月每个客户经理的最大总资产（将客户的资产相加），还要知道客户经理的最大资产是这个月的那一天。

回复赞

王向飞 2010-09-27

要是还不对，你就把你想要的结果贴出来。

回复赞

王向飞 2010-09-27

--brokerid	zct

123	97883

回复赞

王向飞 2010-09-27



--> 数据库版本:

--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22

--> 测试数据：[TB]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') 

AND type in (N'U')) 

DROP TABLE [TB]

GO



---->建表

create table [TB]([brokerid] int,[fundid] int,[zc] int,[date] datetime)

insert [TB]

select 123,145,48988,'20100401' union all

select 123,356,48895,'20100421' union all

select 456,555,5897,'20100526' union all

select 456,444,578,'20100621' union all

select 789,888,693,'20100722' union all

select 789,988,5879,'20100421'

GO



SELECT * FROM [TB] AS T 

WHERE 2>(SELECT COUNT(1) FROM [TB] WHERE [brokerid]=T.[brokerid]  

AND zc>T.zc)

and DATEPART(MM,date)= 4   --指定月

and DATEPART(YY,date) ='2010'--指定年





--> 查询结果

SELECT top 1 t.[brokerid] ,sum([zc]) as zct  FROM [TB] t

where DATEPART(MM,date)= 4   --指定月

and DATEPART(YY,date) ='2010'--指定年

group by  [brokerid] 

order by zct desc

--> 删除表格

--DROP TABLE [TB]

回复赞

guosenwh 2010-09-27

不要意思，我指的是客户经理的最大资产，就是说先要将客户的资产求和。

回复赞

百年树人 2010-09-27

select *
from tb t
where not exists(select 1 from tb where brokerid=t.brokerid and zc>t.zc)
and convert(varchar(7),[date],120)='xxxx-xx'

回复赞

王向飞 2010-09-27

--brokerid	fundid	zc

456	555	5897

回复赞

王向飞 2010-09-27



--> 数据库版本:

--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22

--> 测试数据：[TB]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') 

AND type in (N'U')) 

DROP TABLE [TB]

GO



---->建表

create table [TB]([brokerid] int,[fundid] int,[zc] int,[date] datetime)

insert [TB]

select 123,145,48988,'20100401' union all

select 123,356,48895,'20100421' union all

select 456,555,5897,'20100526' union all

select 456,444,578,'20100621' union all

select 789,888,693,'20100722' union all

select 789,988,5879,'20100421'

GO







--> 查询结果

SELECT [brokerid] ,[fundid],MAX([zc]) as zc  FROM [TB]

where DATEPART(MM,date)=5  --指定月

group by  [brokerid] ,[fundid]

--> 删除表格

--DROP TABLE [TB]