orderspercustomer.sql

来自「Mastering VBNet Include Source Code」· SQL 代码 · 共 17 行

SELECT THE FOLLOWING STORED PROCEDURE DEFINITION, COPY AND PASTE IT ONTO THE QUERY ANALYZER. SELECT THE NORTHWIND DATABASE AND EXECUTE IT BY PRESSING CONTROL+E. 
THE ORDERSPERCUSTOMER STORED PROCEDURE WILL BE ADDED TO THE DATABASE



ALTER PROCEDURE dbo.OrdersPerCustomer
    @CustomerID nchar(5)='ALFKI'
AS

SELECT     Customers.CompanyName, COUNT(Orders.OrderID) AS [Total Orders], Customers.CustomerID, SUM([Order Details].Quantity * Products.UnitPrice) 
                      AS Expr1
FROM         Customers INNER JOIN
                      Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
                      [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
                      Products ON [Order Details].ProductID = Products.ProductID
GROUP BY Customers.CompanyName, Customers.CustomerID
HAVING      (Customers.CustomerID = @CustomerID)