色婷婷狠狠18禁久久YY,CHINESE性内射高清国产,国产女人18毛片水真多1,国产AV在线观看

mysql的三表聯查實例

錢琪琛2年前10瀏覽0評論

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的強大的三表聯查功能是開發人員在編寫查詢語句時的有力工具。