22,206
社区成员
发帖
与我相关
我的任务
分享
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 ##
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
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 行受影响)
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 行受影响)
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 行)
*/
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)