笔记129 合并两个表,取相同字段数值大的部分

笔记129 合并两个表,取相同字段数值大的部分

 1 --合并两个表,取相同字段数值大的部分
 2 --合并两个表,取相同字段数值大的部分,sql语句怎么写?
 3 --表1
 4 --ID    NAME  SEX     MATCH   CHINESE    ENGLISH   PHY  CHEM  TOTAL
 5 --CY001 cred  male     74       68         85      88    83    
 6 --表2
 7 --ID    NAME  SEX     MATCH   CHINESE    ENGLISH   PHY  CHEM  TOTAL
 8 --CY001 cred  male     104      88         73      64    90    
 9 
10 create proc procname
11 as
12 begin
13 update table1
14 set id=t.id,name=t.name
15 ,sex=t.sex,
16 match = t.match,
17 chinese = t.chinese,
18 english = t.english,
19 phy = t.phy,
20 chem = t.chem
21 from (select id,name,sex,MAX(match) as match,MAX(chinese) as chinese,
22 MAX(english) as english,MAX(phy) as phy,MAX(chem) as chem
23 from (select * from table1 Union all select * from table2 ) s
24 group by id,name,sex ) t  
25 end

 

posted @ 2013-08-03 22:51  桦仔  阅读(434)  评论(0)    收藏  举报