国产成人精品亚洲日本在线_欧美精品v国产精品v_亚洲欧洲日韩国产一区二区三区_亚洲乱码一二三四区

外部查詢(xún) (SELECT 列 FROM 表 WHERE 條件);

-- 查找與 ID=1 的用戶(hù)同部門(mén)的其他用戶(hù)
SELECT name, department 
FROM employees 
WHERE department = (
  SELECT department FROM employees WHERE id = 1
);

-- 查找在 "銷(xiāo)售部" 或 "市場(chǎng)部" 的員工訂單
SELECT * FROM orders 
WHERE employee_id IN (
  SELECT id FROM employees WHERE department IN ('銷(xiāo)售部', '市場(chǎng)部')
);

-- 查詢(xún)每個(gè)用戶(hù)的訂單總數(shù)（子查詢(xún)作為列）
SELECT 
  id, 
  name,
  (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- 統(tǒng)計(jì)每個(gè)部門(mén)的平均工資及部門(mén)人數(shù)
SELECT 
  dept_avg.department,
  dept_avg.avg_salary,
  dept_count.emp_count
FROM 
  (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) AS dept_avg
JOIN 
  (SELECT department, COUNT(*) AS emp_count FROM employees GROUP BY department) AS dept_count
ON dept_avg.department = dept_count.department;

-- 查找員工人數(shù)超過(guò)公司平均部門(mén)人數(shù)的部門(mén)
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > (
  SELECT AVG(emp_count) 
  FROM (SELECT COUNT(*) AS emp_count FROM employees GROUP BY department) AS dept_counts
);

-- 查找每個(gè)部門(mén)中工資高于本部門(mén)平均工資的員工
SELECT name, department, salary
FROM employees e1
WHERE salary > (
  SELECT AVG(salary) 
  FROM employees e2 
  WHERE e1.department = e2.department  -- 關(guān)聯(lián)外部查詢(xún)的部門(mén)
);

SELECT e1.name, e1.department 
FROM employees e1
JOIN employees e2 ON e1.department = e2.department
WHERE e2.id = 1 AND e1.id != 1;