Đang chuẩn bị liên kết để tải về tài liệu:
How to do everything with PHP (phần 6)

Tham khảo tài liệu ' how to do everything with php (phần 6)', công nghệ thông tin, kỹ thuật lập trình phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | 234 How to Do Everything with PHP MySQL To find the most popular product the product with the maximum sales over the last year use the following query which groups and sums the orders by product type and then returns the product with the maximum number of orders mysql SELECT p.productDesc SUM o.orderQuantity AS totalSales - FROM products AS p orders AS o - WHERE p.productId o.FK_productId - AND o.orderDate DATE_SUB NOW INTERVAL 1 YEAR - GROUP BY o.FK_productID - ORDER BY totalSales DESC - LIMIT 0 1 --------------------------- ----------- productDesc totalSales --------------------------- ----------- Electronic tape measure 55 --------------------------- ----------- 1 row in set 0.05 sec To see which products were never purchased in 2004 use a left join between the products and orders tables mysql SELECT products.productDesc - FROM products - LEFT JOIN orders ON products.productId orders.FK_productId - AND YEAR orders.orderDate 2004 - WHERE orders.FK_productId IS NULL ------------- productDesc ------------- Alarm clock Toaster ------------- 2 rows in set 0.00 sec And now for the big kahuna Figure 12-1 shows the total purchases made by each facility of each product within the current month together with the pre-tax and post-tax price and a subtotal. TEAM Line mysql SELECT f.facilityName p.productDesc o.orderQuantity p.productUnitPrice as preTaxPrice SUM t.taxPercent as tax-Percent p.productUnitPrice 1 SUM t.taxPercent 100 as postTaxPrice p.productUnitPrice 1 SUM t.taxPercent 100 o. orderQuantity as subTotal FROM facilities as f products as p orders as o products_taxes as pt taxes as t WHERE f.facilityId o.FK facilityId AND o.FK productId p.productId AND p.productId pt.FK productId AND pt.FK taxld t.taxld GROUP BY f.facilityld p.productld o.orderld facilityName 1 productDesc orderQuantity 1 preTaxPrice 1 taxPercent 1 postTaxPrice 1 subTotal 1 Head Office 1 Electric kettle 4 13.50 10.00 14.8500 59.4000 1 Head Office 1 Lawn mower 7 45.00 15.00 51.7500 362.2500 1 Texas .