一个关于检索薪水高于部门平均值的sql文

来源：互联网发布：java comp env jdbc 编辑：程序博客网时间：2024/06/10 19:35

DROP TABLE IF EXISTS `EMPLOYEESALARY`CREATE TABLE `EMPLOYEESALARY` (`ID` INT(5) NOT NULL AUTO_INCREMENT COMMENT 'PRIMARY KEY',`NAME` VARCHAR(20) NOT NULL,`SALARY`  FLOAT(8),`DEPARTMENT` VARCHAR(20),PRIMARY KEY(`ID`))ENGINE MYISAM DEFAULT CHARSET=utf8 COMMENT 'EMPLOYEE TABLE';SELECT `ID`,`NAME` FROM `EMPLOYEESALARY` AS TABLE1LEFT JOIN( SELECT `DEPARTMENT`,AVG(`SALARY`) AS SALARY FROM `EMPLOYEESALARY` GROUP BY `DEPARTMENT` ) AS TABLE2ON TABLE1.`DEPARTMENT` = TABLE2.`DEPARTMENT`WHERE TABLE1.`SALARY` >= TABLE2.`SALARY`;

<span style="font-family: Arial, Helvetica, sans-serif;">mysql的存储过程实现,mysql中没有像oracle中的%rowtype</span>

DELIMITER $$CREATE PROCEDURE `test`.`SalaryCalcProc`()    BEGIN#部门名DECLARE DEPARTMENT1 VARCHAR(20);#薪水DECLARE SALARY1 FLOAT(8);#关于部门平均薪水的游标DECLARE DESTCURSOR CURSOR FOR SELECT `DEPARTMENT`,AVG(`SALARY`) FROM `EMPLOYEESALARY` GROUP BY `DEPARTMENT`;#打开游标OPEN DESTCURSOR;#循环游标READ_LOOP:LOOP  #读取游标值  FETCH DESTCURSOR INTO DEPARTMENT1,SALARY1;  IF FALSE THEN    LEAVE READ_LOOP;  END IF;  #检索出高于部门平均薪水的职员  SELECT * FROM `EMPLOYEESALARY` WHERE SALARY >= SALARY1 AND DEPARTMENT = DEPARTMENT1;  END LOOP;    END$$DELIMITER ;

0 0