A SQL interview example If...else and aggregation function

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