table A id east west 1 10 20 2 15 30 。 table B id east west
我在A中,选出east,west的最大值,insert到B insert into B select id, max(east),max(west) from B group by id然后,我要每天跑一个procedure,把B的数据更新,B始终保持 A中的max(east),max(west)感觉这种sql特别绕,这个该怎么写啊
你的需求同时涉及到INSERT和UPDATE两种操作,建议使用MERGE方法来实现,下面给你个例句,该例句没有经过调试,如果有BUG楼主自己修改下。MERGE INTO B USING (SELECT A.ID,MAX(A.EAST) MAXEAST,MAX(A.WEST) MAXWEST FROM A GROUP BY A.ID) C ON (B.ID = A.ID) WHEN MATCHED THEN UPDATE SET B.EAST = C.MAXEAST, B.WEST = C.MAXWEST WHEN NOT MATCHED THEN INSERT VALUES(C.ID,C.MAXEAST,C.MAXWEST);
或许你可以写个crontab挂在后台让他每天在你规定的时间点进行更新.
USING (SELECT A.ID,MAX(A.EAST) MAXEAST,MAX(A.WEST) MAXWEST FROM A GROUP BY A.ID) C
ON (B.ID = A.ID)
WHEN MATCHED THEN
UPDATE
SET B.EAST = C.MAXEAST,
B.WEST = C.MAXWEST
WHEN NOT MATCHED THEN
INSERT
VALUES(C.ID,C.MAXEAST,C.MAXWEST);