Help to complete a cursor
I am working on cursors. I want to create a cursor to display each customer’s information and the product that he has purchased more. For this aim, I wrote the following cursor:
declare myCursor cursor
for select Customers.CustomerID, Customers.ContactName, Products.ProductName, SUM(Quantity) as Total
from Customers inner join Orders on Customers.CustomerID = Orders.CustomerID
inner join( [Order Details] inner join Products on [Order Details].ProductID = Products.ProductID)
on Orders.OrderID = [Order Details].OrderID
group by Customers.CustomerID, Customers.ContactName, Products.ProductName
--
declare @CustomerID nchar(10), @ContactName nvarchar(30), @ProductName nvarchar(4), @Total int
open myCursor
fetch next from myCursor into @CustomerID, @ContactName , @ProductName, @Total
while @@FETCH_STATUS = 0
begin
begin
print @CustomerID+' '+@ContactName+' '+@ProductName+' '+CAST(@Total as varchar)
fetch next from myCursor into @CustomerID, @ContactName , @ProductName, @Total
end
end
Now it displays that how many times each customers has purchased each product. Now I want to use condition keywords like IN, CONTAIN or EXISTS to compl开发者_C百科ete my cursor to display the product that each customer has purchased more. But I have no idea to do it. Would you please help me?
If I understand correctly, you are looking to show the product that each customer has purchased most. Using your cursor, you could do this by first declaring a table variable such as
declare @customersprocessed table
(
@CustomerID
)
Then, you would need to order your cursor by SUM(Quantity) DESC
so that the top items would rise to the top.
Finally, you would need to modify they inner part of the query to only print if the customer id doesn't already exist in the table, then add the customerid to the table so it wouldn't be printed again.
begin
if ((select count(1) from @customersprocessed where CustomerID = @CustomerID) = 0)
begin
print @CustomerID+' '+@ContactName+' '+@ProductName+' '+CAST(@Total as varchar)
end
else
begin
insert into @customersprocessed select @CustomerID
end
fetch next from myCursor into @CustomerID, @ContactName , @ProductName, @Total
end
This of course is assuming you want to keep the cursor. There would be other ways of doing this using a select of all customers you wanted to show and a subquery which returned the most purchased product.
精彩评论