Oracle SQL测试一

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