Oracle SQL测试1idsnamesmoneysprovince1zhangsan2098A2lisi3000B3wangwu6789C4liumazi4587C5dongjiu3298B
Oracle SQL测试1
id sname smoney sprovince1 zhangsan 2098 A2 lisi 3000 B3 wangwu 6789 C4 liumazi 4587 C5 dongjiu 3298 B6 shiga 4567 A id:合同id sname:姓名 smoney :业绩 sprovince:地区CREATE TABLE test (sid NUMBER,sname VARCHAR2(20 ),smoney NUMBER,sprovince CHAR(1 ))INSERT INTO test VALUES( 1,'zhangsan' ,2098, 'A');INSERT INTO test VALUES( 2,'lisi' ,3000, 'B');INSERT INTO test VALUES( 3,'wangwu' ,6789, 'C');INSERT INTO test VALUES( 4,'liumazi' ,4587, 'C');INSERT INTO test VALUES( 5,'dongjiu' ,3298, 'B');INSERT INTO test VALUES( 6,'shiga' ,4567, 'A');COMMIT;--显示出 业绩 大于同一地区平均值的 合同id 姓名 地区 业绩SELECT t.* FROM test t,(SELECT AVG(smoney) smoney,sprovince FROM test GROUP BY sprovince) t2WHERE t.smoney > t2.smoney AND t.sprovince = t2.sprovince或者SELECT t.* FROM test t WHERE t.smoney > ( SELECT AVG (smoney) FROM test t2 WHERE t.sprovince = t2.sprovince GROUP BY t2.sprovince)--把同一地区的 平均业绩 地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）CREATE TABLE test_temp AS SELECT AVG (t.smoney) smoney,t.sprovince sprovince FROM test t GROUP BY t.sprovince