CREATE TABLE DEPARTMENTS
( DEPARTMENT_ID NUMERIC(4,0),
"DEPARTMENT_NAME" VARCHAR(30 ) CONSTRAINT DEPT_NAME_NN NOT NULL ,
"MANAGER_ID" NUMERIC(6,0),
"LOCATION_ID" NUMERIC(4,0),
);
CREATE TABLE EMPLOYEES
( EMPLOYEE_ID NUMERIC(6,0),
FIRST_NAME VARCHAR(20 ),
LAST_NAME VARCHAR(25 ) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ,
EMAIL VARCHAR(25 ) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ,
PHONE_NUMBER VARCHAR(20 ),
HIRE_DATE DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ,
JOB_ID VARCHAR(10 ) CONSTRAINT "EMP_JOB_NN" NOT NULL ,
SALARY NUMERIC(8,2),
COMMISSION_PCT NUMERIC(2,2),
MANAGER_ID NUMERIC(6,0),
DEPARTMENT_ID NUMERIC(4,0),
);
我需要列出部门名称、平均工资和在该部门工作的获得佣金的员工人数。
SELECT DEPARTMENT_NAME, AVG(SALARY), COUNT(COMMISSION_PCT)
FROM DEPARTMENTS JOIN EMPLOYEES USING (DEPARTMENT_ID);
GROUP BY DEPARTMENT_NAME
这是我到目前为止所得到的,但它给了我一个错误:
“DEPARTMENT_ID”不是可识别的表提示选项。如果打算将其用作表值函数或 CHANGETABLE 函数的参数,请确保将数据库兼容模式设置为 90。
不知道你用的是哪个DBMS。
我认为这会起作用,当你使用INNER JOIN
而不是USING
SELECT DEPARTMENT_NAME, AVG(SALARY), COUNT(COMMISSION_PCT)
FROM DEPARTMENTS
INNER JOIN EMPLOYEES on DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句