Used for e-commerce websites to query data order recommendations for the last 30 days

In some e-commerce websites, it is sometimes necessary to recommend order data generated in the last few days to calculate commodity recommendation information. At the data layer, We need to write some query statements or stored procedures. You can find out which order appears most frequently, sort it in descending order, and select the first 5 as the recommended items.

 

 

select productid, name, description from product where productid in ( select top 5 od2.productid from orderdetails od1 join orderdetails od2 on od1.orderid = od2.orderid where od1.productid = @ productid and od2.productid! = @ Productid and datediff (DD, orders. datecreated, getdate () <30 group by od2.productid order by count (od2.productid) desc )

The table connection operation is used here, and the two instances in the orders table are used for connection. od2 is selected. What other products can be ordered by productid? Here, productid! = @ Productid: Delete the same product number as od2.productid. Then, sort the number of productid occurrences in descending order and select the top 5 digits.