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

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 TABLE1
LEFT JOIN
( SELECT `DEPARTMENT`,AVG(`SALARY`) AS SALARY FROM `EMPLOYEESALARY` GROUP BY `DEPARTMENT` ) AS TABLE2
ON 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 ;