Back in the evening, the classmate said the interview encountered a SQL interview topic, did a bit of their own, summary summary.
The topics are as follows:
The following is a product data sheet (Product ID, color col, num), each of which is available in one or two colors.
Ask for the difference in the number of colors for each product (for the total number of reserved products with only one color)
The solution is as follows:
1. Find out the number of colors for each product first
This is easy, just use GROUP by. The SQL statements are as follows:
1 Select id,col,sum(num) Total from Chanpin group by Id,col
Results:
2. ask for the difference in the number of colors for each product ( for the total number of reserved products with only one color )
Because each product is only one or two:
(1) If there are two, the result is a lot of color products-the number of products with less color;
(2) If there is one, the result is the number of products of this color.
The SQL statements are as follows:
1 Selecta.ID,if(2 Count(*)>1, #判断条件, number of colors3 Max(a.total)- min(a.total), #颜色多的产品数量-number of products with less color4 Max(a.total) #产品数量5 ) Dis6 from (7 SelectId,col,sum(NUM) Total fromChanpinGroup byId,col8 ) A9 Group bya.ID;
Results:
A SQL interview example If...else and aggregation function