Common usage of SQL

Time:2020-8-1

1. where and having

Required condition > = 2 + aggregate condition is required;

  • To analyze which conditions, we need to use aggregate function operation;
  • Where does not need to be aggregated;
  • The total function is obtained;
  • Set up the aggregate function conditions required by group by and having;

2. Join and aggregate function

Total condition + table quantity > = 2

  • The condition of join (the type and condition of join);
  • Group by / aggregate func

Join + group by + Aggregate Func

3. Repeated values

windows function / distinct / join / group by / having clause ;

  • (looking for or removing weight);
  • Distinct, group by and windows func;
  • Finding weight: having and windows func;
  • Simple and crude: use window function (partial by and order by, and then find or remove duplicate).
select * from table group by name having (count *) > 2

sql : qualify
where ---> Join ---> **qualify** ---> select 

4. Find nth

windows function , max

  • Analyze the demand (which is the largest or the smallest);
  • Windows func and max (partial by, order by) sort;
  • What is the largest or the smallest.