笔记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
