高级ql

#select的标量子查询
# 查询每个用户的订单数量
SELECT u.id,u.user_name,u.phone,(SELECT COUNT(1) FROM user_order uo WHERE uo.owner_id=u.id ) as num   FROM sys_user  u;

#查询下过单的用户
SELECT   u.id,u.user_name,u.phone FROM sys_user u WHERE EXISTS (SELECT id FROM user_order uo WHERE uo.owner_id=u.id );

SELECT   u.id,u.user_name,u.phone FROM sys_user u WHERE u.id IN (SELECT DISTINCT uo.owner_id FROM user_order uo  );

 

#查询各部分的工资比本部门平均工资高的人员
# 1查询各部门的平均工资
SELECT
    AVG( salary ) avgSal,emp.department_id FROM  employees emp  GROUP BY  emp.department_id;
    
# 2 将各部门的平均工资作为表关联
SELECT   e.*  FROM  
　　employees e,  ( SELECT AVG( salary ) avgSal, emp.department_id FROM employees emp GROUP BY emp.department_id ) AS avg 
WHERE
    e.department_id = avg.department_id   AND e.salary >= avg.avgSal;