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(客户编号),有临时表也可,谢谢!
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]
456 555 5897
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'
--> 数据库版本:
--> 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'
GOSELECT * 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]
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
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'
GOcreate 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
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'
GOcreate 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