22,300
社区成员




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
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
最好给出完整的表结构,测试数据,计算方法和正确结果.
--brokerid zct
123 97883
--> 数据库版本:
--> 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 fundid zc
456 555 5897
--> 数据库版本:
--> 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]