求最客户经理名下客户资产最大值,谢谢大家回帖!

guosenwh 2010-09-27 04:40:49
brokerid fundid zc date
001 56001 475.00 20100401
001 56002 500.00 20100401
002 56005 475.00 20100401
002 56006 500.00 20100401
003 56007 600.00 20100401
004 56008 700.00 20100401
001 56009 200.00 20100402
001 560010 500.00 20100402
002 560011 475.00 20100402
002 560012 500.00 20100402
003 560013 600.00 20100402
004 560014 700.00 20100402

。。。。

求客户经理(brokerid)当月最大资产,以及资产日期。
select brokerid,date,sum(zc) as zzc into #temptb from tb group by borkerid ,date 这个语句可以资产求和
select brokerid ,max(zzc) from #temptb 这个求最大资产但怎么把最大资产日期加上去呢?
还有更好的解决方法吗,我用的是sql2000,谢谢各位了!



...全文
73 点赞 收藏 16
写回复
16 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
ai_li7758521 2010-09-27
SELECT brokerid,[SUM]=sum(zc),[date]
INTO ##
FROM #2
GROUP BY brokerid,[date]



SELECT 客户经理=brokerid,最大资产=[SUM],时间=[date]
FROM ## T
WHERE NOT EXISTS
(
SELECT 1
FROM ## X
WHERE X.brokerid=T.brokerid
and X.[SUM]>T.[SUM]
)

客户经理 最大资产 时间
---------- --------------------------------------- -----------------------
001 975.00 2010-04-01 00:00:00.000
002 975.00 2010-04-01 00:00:00.000
002 975.00 2010-04-02 00:00:00.000
003 600.00 2010-04-01 00:00:00.000
003 600.00 2010-04-02 00:00:00.000
004 700.00 2010-04-01 00:00:00.000
004 700.00 2010-04-02 00:00:00.000

(7 行受影响)

drop table ##
回复
ai_li7758521 2010-09-27
CREATE TABLE #2
(
brokerid varchar(10),
fundid varchar(10),
zc decimal(18,2),
date datetime
)

INSERT #2
SELECT '001', '56001' , 475.00 ,'20100401'union all
SELECT '001', '56002' , 500.00 ,'20100401'union all
SELECT '002', '56005' , 475.00 ,'20100401'union all
SELECT '002', '56006' , 500.00 ,'20100401'union all
SELECT '003', '56007' , 600.00 ,'20100401'union all
SELECT '004', '56008' , 700.00 ,'20100401'union all
SELECT '001', '56009' , 200.00 ,'20100402'union all
SELECT '001', '560010', 500.00 ,'20100402'union all
SELECT '002', '560011', 475.00 ,'20100402'union all
SELECT '002', '560012', 500.00 ,'20100402'union all
SELECT '003', '560013', 600.00 ,'20100402'union all
SELECT '004', '560014', 700.00 ,'20100402'

SELECT 客户经理=brokerid,最大资产=[SUM],时间=[date]
FROM
(
SELECT brokerid,[SUM]=sum(zc),[date]
FROM #2
GROUP BY brokerid,[date]
) T
WHERE NOT EXISTS
(SELECT 1
FROM
(
SELECT brokerid,[SUM]=sum(zc),[date]
FROM #2
GROUP BY brokerid,[date]
) X
WHERE X.brokerid=T.brokerid and X.[SUM]>T.[SUM]
)


客户经理 最大资产 时间
---------- --------------------------------------- -----------------------
001 975.00 2010-04-01 00:00:00.000
002 975.00 2010-04-01 00:00:00.000
002 975.00 2010-04-02 00:00:00.000
003 600.00 2010-04-01 00:00:00.000
003 600.00 2010-04-02 00:00:00.000
004 700.00 2010-04-01 00:00:00.000
004 700.00 2010-04-02 00:00:00.000

(7 行受影响)

drop table #2
回复
dawugui 2010-09-27
[Quote=引用 13 楼 guosenwh 的回复:]
继续等待,
select brokerid,date,sum(zc) as zzc into #temptb from tb group by borkerid ,date 这个语句可以资产求和
select brokerid ,max(zzc) from #temptb 这个求最大资产但怎么把最大资产日期加上去呢?这个是难点!
[/Quote]
我5楼不是已经把结果都弄出来了?还有什么地方不对?
回复
guosenwh 2010-09-27
继续等待,
select brokerid,date,sum(zc) as zzc into #temptb from tb group by borkerid ,date 这个语句可以资产求和
select brokerid ,max(zzc) from #temptb 这个求最大资产但怎么把最大资产日期加上去呢?这个是难点!
回复
ai_li7758521 2010-09-27
CREATE TABLE #2
(
brokerid varchar(10),
fundid varchar(10),
zc decimal(18,2),
date datetime
)

INSERT #2
SELECT '001', '56001' , 475.00 ,'20100401'union all
SELECT '001', '56002' , 500.00 ,'20100401'union all
SELECT '002', '56005' , 475.00 ,'20100401'union all
SELECT '002', '56006' , 500.00 ,'20100401'union all
SELECT '003', '56007' , 600.00 ,'20100401'union all
SELECT '004', '56008' , 700.00 ,'20100401'union all
SELECT '001', '56009' , 200.00 ,'20100402'union all
SELECT '001', '560010', 500.00 ,'20100402'union all
SELECT '002', '560011', 475.00 ,'20100402'union all
SELECT '002', '560012', 500.00 ,'20100402'union all
SELECT '003', '560013', 600.00 ,'20100402'union all
SELECT '004', '560014', 700.00 ,'20100402'

SELECT 客户经理=brokerid,最大资产=MAX([SUM]),时间=CONVERT(varchar(6),[date],112)
FROM
(
SELECT brokerid,SUM=sum(zc),[date]
FROM #2
GROUP BY brokerid,[date]
) T
GROUP BY brokerid,CONVERT(varchar(6),[date],112)


客户经理 最大资产 时间
---------- --------------------------------------- ------
001 975.00 201004
002 975.00 201004
003 600.00 201004
004 700.00 201004

(4 行受影响)
回复
guosenwh 2010-09-27
没有对客户经理资产求和呢,先要求和再取最大值。
回复
guosenwh 2010-09-27
我把这个语句试了下,在我的表上是不对的。
回复
ws_hgo 2010-09-27
[Quote=引用 7 楼 guosenwh 的回复:]

还是不对,应该有用到max吧
[/Quote]

6楼不是你要的结果吗?

只要到达目的就行啦

什么方法一样
回复
dawugui 2010-09-27
[Quote=引用 7 楼 guosenwh 的回复:]
还是不对,应该有用到max吧
[/Quote]我那个也不对?如果你需要多个条件,自己加进去即可。
回复
guosenwh 2010-09-27
还是不对,应该有用到max吧
回复
ws_hgo 2010-09-27
if object_id('tb') is not null drop table tb
go
create table tb
(
brokerid varchar(10),
fundid int,
zc decimal(19,2),
date datetime
)
insert into tb select '001',56001,475.00,'20100401'
union all select '001',56002,500.00,'20100401'
union all select '002',56005,475.00,'20100401'
union all select '002',56006,500.00,'20100401'
union all select '003',56007,600.00,'20100401'
union all select '004',56008,700.00,'20100401'
union all select '001',56009,200.00,'20100402'
union all select '001',560010,500.00,'20100402'
union all select '002',560011,475.00,'20100402'
union all select '002',560012,500.00,'20100402'
union all select '003',560013,600.00,'20100402'
union all select '004',560014,500.00,'20100402'
go

select brokerid,sum(zc) zc,date from tb
where date in
(
select distinct t.date
from
(
select brokerid,sum(zc) zc,date from tb group by brokerid,date
) t
join
(
select brokerid,sum(zc) zc,date from tb group by brokerid,date
) t1
on t.brokerid=t1.brokerid and t.zc>t1.zc
)
group by brokerid,date

brokerid zc date
---------- --------------------------------------- -----------------------
001 975.00 2010-04-01 00:00:00.000
002 975.00 2010-04-01 00:00:00.000
003 600.00 2010-04-01 00:00:00.000
004 700.00 2010-04-01 00:00:00.000

(4 行受影响)



回复
dawugui 2010-09-27
create table tb(brokerid varchar(10),fundid varchar(10),zc decimal(18,2),date datetime)
insert into tb values('001', '56001' , 475.00 ,'20100401')
insert into tb values('001', '56002' , 500.00 ,'20100401')
insert into tb values('002', '56005' , 475.00 ,'20100401')
insert into tb values('002', '56006' , 500.00 ,'20100401')
insert into tb values('003', '56007' , 600.00 ,'20100401')
insert into tb values('004', '56008' , 700.00 ,'20100401')
insert into tb values('001', '56009' , 200.00 ,'20100402')
insert into tb values('001', '560010', 500.00 ,'20100402')
insert into tb values('002', '560011', 475.00 ,'20100402')
insert into tb values('002', '560012', 500.00 ,'20100402')
insert into tb values('003', '560013', 600.00 ,'20100402')
insert into tb values('004', '560014', 700.00 ,'20100402')
go

select m.* from
(
select brokerid ,sum(zc) zc,date from tb group by brokerid,date
) m where not exists (select 1 from
(
select brokerid ,sum(zc) zc,date from tb group by brokerid,date
) n where n.brokerid = m.brokerid and (n.zc > m.zc or (n.zc = m.zc and n.date < m.date))
)
order by m.brokerid


drop table tb

/*
brokerid zc date
---------- ---------------------------------------- ------------------------------------------------------
001 975.00 2010-04-01 00:00:00.000
002 975.00 2010-04-01 00:00:00.000
003 600.00 2010-04-01 00:00:00.000
004 700.00 2010-04-01 00:00:00.000

(所影响的行数为 4 行)
*/
回复
guosenwh 2010-09-27
结果,4月客户经理资产峰值及峰值日期:
brokerid zc date
001 975 20100401
002 975 20100401
003 600 20100401
004 700 20100401

不好意思,这个数据可能没有设计好,不知大家是否明白我的意图。

回复
dawugui 2010-09-27
select t.* from tb t where zc = (select max(zc) from tb where brokerid = t.brokerid and datediff(mm,date,t.date) = 0)

select t.* from tb t where not exists (select 1 from tb where brokerid = t.brokerid and datediff(mm,date,t.date) = 0 and zc > t.zc)
回复
ws_hgo 2010-09-27
结果是什么啊??
回复
dawugui 2010-09-27
就你的数据,结果是什么?
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-09-27 04:40
社区公告
暂无公告