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

[guosenwh]

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（客户编号），有临时表也可，谢谢！

[王向飞]

--> 数据库版本:
--> 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]

[王向飞]

--brokerid	fundid	zc
456	555	5897

[百年树人]

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'

[guosenwh]

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

[王向飞]

--> 数据库版本:
--> 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]

[王向飞]

--brokerid	zct
123	97883

[王向飞]

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

[guosenwh]

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

[王向飞]

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

[dawugui]

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

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

[guosenwh]

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

[njz168]

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

[王向飞]

把你想要的结果贴出来

[njz168]

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

[guosenwh]

不好意思，我再发个帖。