Under SQL SERVER: 1, recursively query the sub-categories under the parent classification. 2. Check the top two items in each product category SQL

1. Recursively query the sub-categories under the parent classification. Table Design:

Sql:

--CTE Statement (for later versions of MSSQL2005) withCte_testnavi (id,name,pid) as(--This is the query statementSELECTId,name,pid fromNaviWHEREName='Automotive'Union  All--This is the part where recursion is required, and the CTE itself calls the completion loop recursive lookupSELECTA.id,a.name,a.pid fromNavi aINNER JOINCte_testnavi b on(A.pid=b.id))Select *  fromCte_testnavi

2. Check the top two items in each product category SQL

Table Design:

Sql:

--ask for the top two most expensive items under each item by category--over (partition by C.classid order by I.price DESC) the over window function avoids the case where group by is not included in the child column.--but the window function will return multiple rows of results, according to the use of the decision, such as here I want to follow the CLASSID product category ID to group--the rank function is defined by Microsoft as: Returns the rank of each row within the result set partition. The rank of a row is a number of rows before the line involved. --like here I sort by price, take out the most expensive items in each category from top to bottomSELECT *  from(SELECTI.price,i.commodityname,c.classname,rank () Over(Partition byC.classidOrder  byI.priceDESC) Rank2 fromCommodityinfo iINNER JOINCommodityclass C onI.commodityclass=C.classid) RWHERERank2<=2;

Under SQL SERVER: 1, recursively query the sub-categories under the parent classification. 2. Check the top two items in each product category SQL