SQL自动生成

SELECT d.dept_name, ROUND(AVG(e.salary), 2) AS avg_salary, COUNT() AS emp_count FROM departments AS d JOIN employees AS e ON e.department_id = d.department_id WHERE e.status = 'active' AND e.hire_date >= CURRENT_DATE - INTERVAL '2 years' GROUP BY d.department_id, d.dept_name HAVING AVG(e.salary) > 12000 AND COUNT() >= 5 ORDER BY avg_salary DESC, d.dept_name ASC LIMIT 50;

WITH txn_agg AS ( SELECT it.product_id, SUM(CASE it.txn_type WHEN 'IN' THEN it.quantity WHEN 'ADJ' THEN it.quantity WHEN 'OUT' THEN -it.quantity ELSE 0 END) AS net_qty FROM inventory_transactions AS it WHERE it.txn_date >= DATEADD(day, -365, GETDATE()) AND it.txn_type IN ('IN', 'OUT', 'ADJ') GROUP BY it.product_id ) SELECT TOP (100) p.sku, p.product_name, p.category, ISNULL(ta.net_qty, 0) AS current_stock, p.reorder_point - ISNULL(ta.net_qty, 0) AS need_qty FROM products AS p LEFT JOIN txn_agg AS ta ON ta.product_id = p.product_id WHERE p.is_discontinued = 0 AND ISNULL(ta.net_qty, 0) < p.reorder_point ORDER BY need_qty DESC, current_stock ASC;