作者:诗民宜洁兰丰 | 来源:互联网 | 2014-07-08 01:11
OracleSQL测试1Sql代码www.2cto.comidsnamesmoneysprovince1zhangsan2098A2lisi3000B3wangwu6789C4liumazi458...SyntaxHighlighter.all();
Sql代码 www.2cto.com
id sname smoney sprovince
1 zhangsan 2098 A
2 lisi 3000 B
3 wangwu 6789 C
4 liumazi 4587 C
5 dongjiu 3298 B
6 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) t2
WHERE 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