--设查询2005年12月的数据: INSERT INTO SYS_MONTH(YEAR,MONTH,MAX_DATA,MAX_TIME) SELECT SUBSTR(T.YMD,1,4), SUBSTR(T.YMD,5,2), T.SYS_DATA, SUBSTR(T.YMD,7,2) FROM SYS_DAY as T WHERE SUBSTR(YMD,1,6)='200512' AND NOT EXISTS(SELECT * FROM SYS_DAY WHERE SUBSTR(YMD,1,6)=SUBSTR(T.YMD,1,6) AND SYS_DATA>T.SYS_DATA)
UPDATE SYS_MONTH SET MAX_DATA=(SELECT T.SYS_DATA FROM SYS_DAY T WHERE SUBSTR(T.YMD,1,6)=SYS_MONTH.YEAR||SYS_MONTH.MONTH AND NOT EXISTS(SELECT * FROM SYS_DAY WHERE SUBSTR(YMD,1,6)=SUBSTR(T.YMD,1,6) AND SYS_DATA>T.SYS_DATA)), MAX_TIME=(SELECT SUBSTR(T.YMD,7,2) FROM SYS_DAY T WHERE SUBSTR(T.YMD,1,6)=SYS_MONTH.YEAR||SYS_MONTH.MONTH AND NOT EXISTS(SELECT * FROM SYS_DAY WHERE SUBSTR(YMD,1,6)=SUBSTR(T.YMD,1,6) AND SYS_DATA>T.SYS_DATA)) FROM SYS_MONTH
存储过程里很好实现啊:delete from SYS_MONTH;insert into SYS_MONTH(YEAR,MONTH,MAX_DATA,MAX_TIME) select distinct substr(ymd,1,4), substr(ymd,5,2), max(SYS_DATA) over(partition by substr(ymd,1,4),substr(ymd,5,2)), first_value(YMD) over(partition by substr(ymd,1,4),substr(ymd,5,2) order by SYS_DATA desc) from SYS_DAY
寻兄。 first_value(YMD) over(partition by substr(ymd,1,4),substr(ymd,5,2) order by SYS_DATA desc) 这样检索出来的时间跟那个最大值的时间对不上啊
UPDATE SYS_MONTH SET MAX_DATA=(SELECT T.SYS_DATA FROM SYS_DAY T WHERE SUBSTR(T.YMD,1,6)=SYS_MONTH.YEAR||SYS_MONTH.MONTH AND NOT EXISTS(SELECT * FROM SYS_DAY WHERE SUBSTR(YMD,1,6)=SUBSTR(T.YMD,1,6) AND SYS_DATA>T.SYS_DATA)), MAX_TIME=(SELECT SUBSTR(T.YMD,7,2) FROM SYS_DAY T WHERE SUBSTR(T.YMD,1,6)=SYS_MONTH.YEAR||SYS_MONTH.MONTH AND NOT EXISTS(SELECT * FROM SYS_DAY WHERE SUBSTR(YMD,1,6)=SUBSTR(T.YMD,1,6) AND SYS_DATA>T.SYS_DATA)) FROM SYS_MONTH ----------------- yes,要的就是这个结果。不过后面的两行多余了。。还有更新的速度不是很快。SYS_MONTH这个表的记录才49条却要了2.357秒。 SYS_DAY也才1477条记录。不过还是很感谢你。。也感谢每个参与过得朋友。谢谢!!!!!!!!!
INSERT INTO SYS_MONTH(YEAR,MONTH,MAX_DATA,MAX_TIME)
SELECT
SUBSTR(T.YMD,1,4),
SUBSTR(T.YMD,5,2),
T.SYS_DATA,
SUBSTR(T.YMD,7,2)
FROM
SYS_DAY as T
WHERE
SUBSTR(YMD,1,6)='200512'
AND
NOT EXISTS(SELECT
*
FROM
SYS_DAY
WHERE
SUBSTR(YMD,1,6)=SUBSTR(T.YMD,1,6)
AND
SYS_DATA>T.SYS_DATA)
但是我现在要的的更新月记录表里的max_data,和max_time啊.
更新?
兄弟,不明白你的意思,照我的理解,如果不是插入是更新的话,你直接把上面那位高人的insert改为相应的update不就好了吗
SET
MAX_DATA=(SELECT
T.SYS_DATA
FROM
SYS_DAY T
WHERE
SUBSTR(T.YMD,1,6)=SYS_MONTH.YEAR||SYS_MONTH.MONTH
AND
NOT EXISTS(SELECT
*
FROM
SYS_DAY
WHERE
SUBSTR(YMD,1,6)=SUBSTR(T.YMD,1,6)
AND
SYS_DATA>T.SYS_DATA)),
MAX_TIME=(SELECT
SUBSTR(T.YMD,7,2)
FROM
SYS_DAY T
WHERE
SUBSTR(T.YMD,1,6)=SYS_MONTH.YEAR||SYS_MONTH.MONTH
AND
NOT EXISTS(SELECT
*
FROM
SYS_DAY
WHERE
SUBSTR(YMD,1,6)=SUBSTR(T.YMD,1,6)
AND
SYS_DATA>T.SYS_DATA))
FROM
SYS_MONTH
select distinct substr(ymd,1,4),
substr(ymd,5,2),
max(SYS_DATA) over(partition by substr(ymd,1,4),substr(ymd,5,2)),
first_value(YMD) over(partition by substr(ymd,1,4),substr(ymd,5,2) order by SYS_DATA desc)
from SYS_DAY
first_value(YMD) over(partition by substr(ymd,1,4),substr(ymd,5,2) order by SYS_DATA desc)
这样检索出来的时间跟那个最大值的时间对不上啊
SET
MAX_DATA=(SELECT
T.SYS_DATA
FROM
SYS_DAY T
WHERE
SUBSTR(T.YMD,1,6)=SYS_MONTH.YEAR||SYS_MONTH.MONTH
AND
NOT EXISTS(SELECT
*
FROM
SYS_DAY
WHERE
SUBSTR(YMD,1,6)=SUBSTR(T.YMD,1,6)
AND
SYS_DATA>T.SYS_DATA)),
MAX_TIME=(SELECT
SUBSTR(T.YMD,7,2)
FROM
SYS_DAY T
WHERE
SUBSTR(T.YMD,1,6)=SYS_MONTH.YEAR||SYS_MONTH.MONTH
AND
NOT EXISTS(SELECT
*
FROM
SYS_DAY
WHERE
SUBSTR(YMD,1,6)=SUBSTR(T.YMD,1,6)
AND
SYS_DATA>T.SYS_DATA))
FROM
SYS_MONTH
-----------------
yes,要的就是这个结果。不过后面的两行多余了。。还有更新的速度不是很快。SYS_MONTH这个表的记录才49条却要了2.357秒。 SYS_DAY也才1477条记录。不过还是很感谢你。。也感谢每个参与过得朋友。谢谢!!!!!!!!!