高手们看看这个语句如何写呀?想的脑袋都疼.

[whxxr]

表结构如下:
MAIN:

DWDM 单位代码
GRDM 个人代码
BGDATE 日期
BGLX 类型
索引是:DWDM,GRDM

记录如下:
0001 1 20060101 1
0001 2 20060101 2
0001 1 20060301 2
0002 1 20060301 1
............
这个表里有30多万条记录,我要取出每个单位每位员工最大日期的类型
我建了一张临时表
结构如下:
TMP1:

DWDM 单位代码
GRDM 个人代码
BGDATE 日期
BGLX 类型
索引也是:DWDM,GRDM

1.我先插入日期最大的单位代码和个人代码
SQL:INSERT INTO TMP1(DWDM,GRDM,BGDATE) SELECT DWDM,GRDM,MAX(BGDATE) AS BGDATE
FROM MAIN GROUP BY DWDM,GRDM
这段代码执行的时间还是可以的.有个3-5分钟就完成了.
2.然后运行替换语句:
SQL:UPDATE TMP1 SET TMP1.BGLX=(SELECT BGLX FROM MAIN WHERE TMP1.DWDM=MAIN.DWDM AND TMP1.GRDM=MAIN.GRDM AND TMP1.BGDATE=MAIN.BGDATE)
这段代码执行速度很慢,我算了一下,最少也要45分钟.(不知道为什么这么慢,完成30万条记录的替换怎么会哪些慢呀)
我不能改表结构,因为是给人家二次开发的.

我还想了一种办法:
SQL:INSERT INTO TMP1(DWDM,GRDM,BGLX) SELECT DWDM,GRDM,BGLX FROM MAIN WHERE TMP1.DWDM=MAIN.DWDM AND TMP1.GRDM=MAIN.GRDM AND MAIN.BGDATE=(SELECT MAX(BGDATE) FROM MAIN WHERE TMP1.DWDM=MAIN.DWDM AND TMP1.GRDM=MAIN.GRDM)
这种方式在试图中用过,不过速度也是很慢.

曾经写过这要的SQL,不过执行不能过
(INSERT INTO TMP1(DWDM,GRDM,BGDATE,BGLX) SELECT DWDM,GRDM,MAX(BGDATE) AS BGDATE,BGLX
FROM MAIN GROUP BY DWDM,GRDM,BGDATE)

哪位高手能看看能把SQL语句优化一下呀.有个10分8分的就执行完呀.
机器还是可以的.1G的内存.

[duongchuang]

insert into tmp1
(select t.dwdm, t.grdm, t.bgdate, t.bglx
from (select dwdm,
grdm,
bgdate,
bglx,
row_number() over (partition by dwdm, grdm order by bgdate desc) as rid
from main) t
where t.rid = 1)

你试试这个咋样？

[njcao]

不需要临时表
select
dwdm,
grdm,
bgdate,
bglx
from
(
select
dwdm,
grdm,
bgdate,
bglx,
row_number() over(
partition by dwdm, grdm
order by dwdm, grdm, bgdate desc
) rowno
from
main
)
where rowno = 1

[沝林]

试试这样是不是快点：

INSERT INTO TMP1(DWDM,GRDM,BGLX) SELECT distinct DWDM,GRDM,first_value(BGLX) over(partition by DWDM,GRDM order by BGDATE desc) BGDATE FROM MAIN

[hyrongg]

SQL> CREATE TABLE MAIN(DWDM VARCHAR2(10),
2 GRDM VARCHAR2(10),
3 BGDATE NVARCHAR2(10),
4 BGLX NUMBER);

Table created

SQL> INSERT INTO MAIN SELECT '0001',1,'20060101',1 FROM DUAL
2 UNION ALL SELECT '0001',2,'20060101',2 FROM DUAL
3 UNION ALL SELECT '0001',1,'20060301',2 FROM DUAL
4 UNION ALL SELECT '0002',1,'20060301',1 FROM DUAL
5 UNION ALL SELECT '0002',1,'20060401',1 FROM DUAL
6 UNION ALL SELECT '0002',2,'20060501',2 FROM DUAL;

6 rows inserted
SQL> SELECT * FROM MAIN;

DWDM GRDM BGDATE BGLX
---------- ---------- -------------------- ----------
0001 1 20060101 1
0001 2 20060101 2
0001 1 20060301 2
0002 1 20060301 1
0002 1 20060401 1
0002 2 20060501 2

6 rows selected
SQL> SELECT A.DWDM,A.GRDM,A.BGDATE,A.BGLX FROM MAIN A
2 INNER JOIN (SELECT DWDM,GRDM,MAX(BGDATE) BGDATE FROM MAIN GROUP BY DWDM,GRDM)
3 B ON A.GRDM=B.GRDM AND A.BGDATE=B.BGDATE AND A.DWDM=B.DWDM;

DWDM GRDM BGDATE BGLX
---------- ---------- -------------------- ----------
0001 1 20060301 2
0001 2 20060101 2
0002 1 20060401 1
0002 2 20060501 2

直接找到你要的结果