【急求】汇总显示问题...

Question

例如：
T1(uid,hkid)
T2(uid,department,effdate)
T3(uid,amount)数据：
T1:
111,aaaa
222,aaaa
333,bbbb
444,cccc
......T2:
111,dept1,2011-01-01
111,dept2,2011-05-01
222,dept1,2010-10-01
333,dept3,2011-01-01
444,dept1,2008-04-01
......T3:
111,1000.00
111,5000.00
222,2000.00
333,3000.00
444,1000.00
444,500.00按hkid统计金额并显示当前最新生效的对应部门等信息，预想参考结果如下：aaaa,8000.00,dept2,2011-05-01
bbbb,3000.00,dept3,2011-01-01
cccc,1500.00,dept1,2008-04-01
......如何写比较好.....谢谢！

Answer 1

select a.hkid,sum(c.amount) from T1 a,
  (select row_number() over(partition by uid order by effdate desc) no,* from T2) b,
  T3 c,max(case when b.no=1 then department end),
        max(case when b.no=1 then effdate end)
 where a.uid=b.uid and b.uid=c.uid
group by a.hkid

Answer 2

select a.hkid,sum(c.amount),max(case when b.no=1 then department end),
                            max(case when b.no=1 then effdate end)
 from T1 a,
  (select row_number() over(partition by uid order by effdate desc) no,* from T2) b,
  T3 c
 where a.uid=b.uid and b.uid=c.uid
 group by a.hkid

Answer 3

select a.hkid,sum(b.amount),
       [department]=(select top 1 department from T2 
                 where uid=a.uid order by effdate),
        max(effdate)
from T1 a,T3 b,T2 C
 where a.uid=b.uid and a.uid=c.uid
group by a.uid,a.hkid

Answer 4

select a.hkid,sum(c.amount),
MAX(b.department),
MAX(b.effdate)
from T1 a,T3 c,(SELECT uid,MAX(department) AS department,MAX(effdate) AS effdate FROM t2 GROUP BY uid) b
 where a.uid=b.uid and b.uid=c.uid
group by a.hkid--result
hkid                                         
---------- ---------------------- ---------- -----------------------
aaaa       8000                   dept2      2011-05-01 00:00:00.000
bbbb       3000                   dept3      2011-01-01 00:00:00.000
cccc       1500                   dept1      2008-04-01 00:00:00.000(3 row(s) affected)

Answer 5

CREATE TABLE t1
(
uid VARCHAR(10),
hkid VARCHAR(10)
)INSERT INTO t3
SELECT '111',1000
UNION ALL
SELECT '111',5000
UNION ALL
SELECT '222',2000
UNION ALL
SELECT '333',3000
UNION ALL
SELECT '444',1000
UNION ALL
SELECT '444',500CREATE TABLE t2
(
uid VARCHAR(10),
department VARCHAR(10),
effdate DATETIME
)CREATE TABLE t3
(
uid VARCHAR(10),
amount FLOAT
)select a.hkid,sum(c.amount) AS amount,
MAX(b.c1) AS department,
MAX(b.c2) AS effdate
from T1 a,T3 c,(SELECT uid,MAX(department) AS c1,MAX(effdate) AS c2 FROM t2 GROUP BY uid) b
 where a.uid=b.uid and b.uid=c.uid
group by a.hkidhkid       amount                 department effdate
---------- ---------------------- ---------- -----------------------
aaaa       8000                   dept2      2011-05-01 00:00:00.000
bbbb       3000                   dept3      2011-01-01 00:00:00.000
cccc       1500                   dept1      2008-04-01 00:00:00.000(3 row(s) affected)