MySQL是一款流行的關系型數據庫管理系統,其三表聯查功能十分強大,適用于多表數據的查詢和分析。以下是一些三表聯查的實例:
SELECT customers.customerName, orders.orderDate, orderdetails.quantityOrdered * orderdetails.priceEach AS revenue FROM customers JOIN orders ON customers.customerNumber = orders.customerNumber JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber WHERE orders.orderDate BETWEEN '2018-01-01' AND '2018-12-31' ORDER BY revenue DESC
上述SQL查詢語句用于查詢2018年每個客戶在訂單中產生的總收入,包括客戶名稱、訂單日期和收入,并按收入從高到低排序。
SELECT employees.firstName, employees.lastName, orders.orderDate, SUM(orderdetails.quantityOrdered * orderdetails.priceEach) AS revenue FROM employees JOIN customers ON employees.employeeNumber = customers.salesRepEmployeeNumber JOIN orders ON customers.customerNumber = orders.customerNumber JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber WHERE orders.orderDate BETWEEN '2018-01-01' AND '2018-12-31' GROUP BY employees.employeeNumber ORDER BY revenue DESC
此查詢語句用于獲取在2018年內每個銷售代表負責的客戶訂單中的總收入。它返回員工的名字、姓氏、訂單日期和總收入,并按收入從高到低排序。
SELECT products.productName, categories.categoryName, suppliers.companyName, products.buyPrice, products.sellPrice, SUM(orderdetails.quantityOrdered) AS totalSales FROM products JOIN categories ON products.categoryID = categories.categoryID JOIN suppliers ON products.supplierID = suppliers.supplierID JOIN orderdetails ON products.productCode = orderdetails.productCode GROUP BY products.productCode ORDER BY totalSales DESC
此查詢語句用于獲取每件產品的銷售情況,包括產品名稱、類別名稱、供應商名稱、購買價格、銷售價格和總銷售量,并按銷售量從高到低排序。
以上是三表聯查實例的簡介,MySQL的強大的三表聯查功能是開發人員在編寫查詢語句時的有力工具。