sql代码:
CREATE TABLE EMPLOYEE_ORDERS (
TOTAL_HANDLED INT,
CONSTRAINT PK_TOTAL PRIMARY KEY (TOTAL_HANDLED)
);
INSERT INTO EMPLOYEE_ORDERS (
SELECT COUNT(ORDERS.EMPLOYEE_ID), EMPLOYEE.EMPLOYEE_ID
FROM ORDERS LEFT JOIN EMPLOYEE
ON ORDERS.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID
GROUP BY EMPLOYEE.EMPLOYEE_ID
ORDER BY COUNT(ORDERS.EMPLOYEE_ID));
我已经尝试:插入雇员_orders(total_handled((选择....
我试图返回所有处理超过0个订单的员工的ID,任何建议都将不胜感激
您的子查询返回两个值,但表中只有一个列。您需要重写您的子查询以摆脱COUNT
值并在HAVING
子句中使用它以过滤员工ID:
INSERT INTO EMPLOYEE_ORDERS
SELECT EMPLOYEE.EMPLOYEE_ID
FROM ORDERS
LEFT JOIN EMPLOYEE ON ORDERS.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID
GROUP BY EMPLOYEE.EMPLOYEE_ID
HAVING COUNT(ORDERS.EMPLOYEE_ID) > 0
请注意,您的子查询没有任何ORDER BY
,因为它不会对插入表中的行有任何影响。
还请注意,通过用JOIN
替换LEFT JOIN
并交换表的顺序,您将自动仅获取具有1个或更多匹配订单的员工,从而使您可以简化查询:
INSERT INTO EMPLOYEE_ORDERS
SELECT DISTINCT EMPLOYEE.EMPLOYEE_ID
FROM EMPLOYEES
JOIN ORDERS ON ORDERS.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID
如果您只是在寻找订单表的员工列表比较,并以订单计数为0的员工返回0,我可能会做类似的事情:
SELECT DISTINCT emp.Employee_ID
, COUNT(Order_ID)
FROM ORDERS ord
RIGHT OUTER JOIN EMPLOYEES emp ON emp.Employee_ID = ord.Employee_ID
GROUP BY emp.Employee_ID
HAVING COUNT(Order_ID) > 0
我正在尝试退还所有处理超过0订单的员工的ID
换句话说,您只需要在ORDERS
表中的员工。最好的方法是:
SELECT DISTINCT o.EMPLOYEE_ID
FROM ORDERS o
如果您也需要计数,请使用GROUP BY
:
SELECT o.EMPLOYEE_ID, COUNT(*)
FROM ORDERS o
GROUP BY o.EMPLOYEE_ID;
无需JOIN
。您正在寻找的员工已经在ORDERS
中。