送佛送西天,测试环境也给大家:create table hz (
dj varchar(10) null default '',
riqi varchar(10) null default '')create table mx(
dj varchar(10) null default '',
fd1 varchar(10) null default '',
fd2 varchar(10) null default '',
je decimal(14,2) null default '')001 ww ww1 100.00
001 ww ww2 110.00
001 ee ee1 101.00
002 ee ee2 111.00
002 ww ww1 200.00
002 ww ww2 310.00
003 ee ee1 400.00
003 ee ee2 510.00
003 ww ww1 600.00
004 ww ww2 710.00
005 ww ww1 800.00
005 ww ww2 910.00insert into hz
select '001','2004-12-21'union all select '002','2004-12-22'
union all select '003','2004-12-23'
union all select '004','2004-12-24'
union all select '005','2004-12-25'
insert into mx
select '001','ww','ww1',100.00
union all select '001','ww','ww2',110.00
union all select '001','ee','ee1',101.00
union all select '002','ee','ee2',111.00
union all select '002','ww','ww1',200.00
union all select '002','ww','ww2',310.00
union all select '003','ee','ee1',400.00
union all select '003','ee','ee2',510.00
union all select '003','ww','ww1',600.00
union all select '004','ww','ww2',710.00
union all select '005','ww','ww1',800.00
union all select '005','ww','ww2',910.00
dj varchar(10) null default '',
riqi varchar(10) null default '')create table mx(
dj varchar(10) null default '',
fd1 varchar(10) null default '',
fd2 varchar(10) null default '',
je decimal(14,2) null default '')001 ww ww1 100.00
001 ww ww2 110.00
001 ee ee1 101.00
002 ee ee2 111.00
002 ww ww1 200.00
002 ww ww2 310.00
003 ee ee1 400.00
003 ee ee2 510.00
003 ww ww1 600.00
004 ww ww2 710.00
005 ww ww1 800.00
005 ww ww2 910.00insert into hz
select '001','2004-12-21'union all select '002','2004-12-22'
union all select '003','2004-12-23'
union all select '004','2004-12-24'
union all select '005','2004-12-25'
insert into mx
select '001','ww','ww1',100.00
union all select '001','ww','ww2',110.00
union all select '001','ee','ee1',101.00
union all select '002','ee','ee2',111.00
union all select '002','ww','ww1',200.00
union all select '002','ww','ww2',310.00
union all select '003','ee','ee1',400.00
union all select '003','ee','ee2',510.00
union all select '003','ww','ww1',600.00
union all select '004','ww','ww2',710.00
union all select '005','ww','ww1',800.00
union all select '005','ww','ww2',910.00
,T1.fd2
,sum(T1.je)
,T2.riqi
From dbo.mx T1
inner join dbo.hz T2
on T1.dj = T2.dj
inner join(select
T3.fd1 as fd1
,T3.fd2 as fd2
,max(T4.riqi) as riqi
from dbo.mx T3
inner join dbo.hz T4
on T3.dj = T4.dj
Group By T3.fd1
,T3.fd2) as S1
on T1.fd1 = S1.fd1
and T1.fd2 = S1.fd2
and T2.riqi = S1.riqi
Group By T1.fd1
,T1.fd2
,T2.riqi
order by T2.riqi DESC
应该可以把!
group by b.fd1,b.fd2select dj,fd1,fd2,rq into #p from #t a, hz b
where a.rq = b.riqiselect a.fd1, a.fd2, b.je, a.rq from #p a, mx b
where a.dj = b.dj and a.fd1 = b.fd1 and a.fd2 = b.fd2
drop table #tdrop table #p
t2.fd2,
t2.je,
t1.riqi
from t1,t2
where t1.dj=t2.dj
and t1.riqi in (select max(riqi) from t1,t2
where t1.dj=t2.dj
group by fd1,fd2)
and t2.je in(select max(je) from t1,t2
where t1.dj=t2.dj
group by fd1,fd2)