MySQL技巧 | 基本篇

select 
  round(
    avg(
      order_date = customer_pref_delivery_date
    ) * 100, 
    2
  ) as immediate_percentage 
from 
  Delivery 
where 
  (customer_id, order_date) in (
    select 
      customer_id, 
      min(order_date) 
    from 
      Delivery 
    group by 
      customer_id
  );

select 
  w1.id 
from 
  Weather w1 
  join Weather w2 on datediff(w1.recordDate, w2.recordDate) = 1 
where 
  w1.temperature > w2.temperature;

select 
  date_format(trans_date, '%Y-%m') as month, 
  country, 
  count(*) as trans_count, 
  sum(
    if(state = 'approved', 1, 0)
  ) as approved_count, 
  sum(amount) as trans_total_amount, 
  sum(
    if(state = 'approved', amount, 0)
  ) as approved_total_amount 
from 
  Transactions 
group by 
  month, 
  country;

select 
  round(
    count(distinct player_id) / (
      select 
        count(distinct player_id) 
      from 
        Activity
    ), 
    2
  ) as fraction 
from 
  Activity 
where 
  (player_id, event_date) in (
    select 
      player_id, 
      min(event_date) + interval 1 day 
    from 
      Activity 
    group by 
      player_id
  );

select 
  p.product_id, 
  ifnull(
    round(
      sum(p.price * u.units) / sum(u.units), 
      2
    ), 
    0
  ) average_price 
from 
  Prices p 
  left join UnitsSold u on p.product_id = u.product_id 
  and u.purchase_date between p.start_date 
  and p.end_date 
group by 
  p.product_id;

select 
  e.name 
from 
  Employee e 
  join (
    select 
      managerId 
    from 
      Employee 
    where 
      managerId is not null 
    group by 
      managerId 
    having 
      count(id) >= 5
  ) sub on e.id = sub.managerId;

with exam_record_densed as (
    select
        uid,
        sum(if(score, 0, 1)) incomplete_cnt,
        round(sum(if(score, 0, 1)) / count(*), 3) incomplete_rate
    from
        exam_record
    group by
        uid
),
exam as (
    select
        u.uid,
        level,
        incomplete_cnt,
        incomplete_rate
    from
        exam_record_densed e
        right join user_info u on e.uid = u.uid
),
org_result as (
    select
        uid,
        ifnull(incomplete_cnt, 0) imcomplete_cnt,
        ifnull(incomplete_rate, 0.000) incomplete_rate
    from
        exam
    where
        level = 0
        and exists (
            select
                1
            from
                exam
            where
                level = 0
                and incomplete_cnt > 2
        )
    union all
    select
        uid,
        incomplete_cnt,
        incomplete_rate
    from
        exam
    where
        incomplete_cnt is not null
        and not exists (
            select
                1
            from
                exam
            where
                level = 0
                and incomplete_cnt > 2
        )
)
select
    *
from
    org_result
order by
    incomplete_rate;

select 
  * 
from 
  (
    select 
      case when mod(id, 2) = 0 then id - 1 when mod(id, 2) = 1 
      and id < (
        select 
          max(id) 
        from 
          Seat
      ) then id + 1 else id end as id, 
      student 
    from 
      Seat
  ) sub 
order by 
  id;

with ranked_scores as (
    select
        id,
        job,
        score,
        row_number() over(
            partition by job
            order by
                score desc
        ) row_order,
        rank() over(
            partition by job
            order by
                score desc
        ) t_rank,
        count(*) over(partition by job) total_count
    from
        grade
),
median_score as (
    select
        id,
        job,
        score,
        row_order,
        t_rank,
        ceil(total_count / 2.0) mid_point,
        case
            when mod(total_count, 2) = 0 then ceil(total_count / 2.0) + 1
            else ceil(total_count / 2.0)
        end second_mid_point
    from
        ranked_scores
)
select
    distinct id,
    job,
    score,
    t_rank
from
    median_score
where
    row_order = mid_point
    or row_order = second_mid_point
order by
    id;

with acum_cnt as (
  select 
    grade, 
    number, 
    sum(number) over(
      order by 
        grade
    ) acum_num 
  from 
    class_grade
), 
get_median_num as (
  select 
    ceil(
      max(acum_num) / 2.0
    ) left_median, 
    floor(
      max(acum_num) / 2.0 + 1
    ) right_median 
  from 
    acum_cnt
) (
  select 
    grade 
  from 
    acum_cnt 
  where 
    acum_num >= (
      select 
        left_median 
      from 
        get_median_num
    ) 
  limit 
    1
) 
union 
  (
    select 
      grade 
    from 
      acum_cnt 
    where 
      acum_num >= (
        select 
          right_median 
        from 
          get_median_num
      ) 
    limit 
      1
  );

select 
  ifnull(
    (
      select 
        distinct salary 
      from 
        Employee 
      order by 
        salary desc 
      limit 
        1 offset 1
    ), 
    null
  ) as SecondHighestSalary;

with
    record_in_range as (
        select
            city,
            event_time org_time,
            coalesce(start_time, finish_time, end_time) term_time
        from
            tb_get_car_record r
            left join tb_get_car_order o on r.order_id = o.order_id
        where
            date (event_time) between '2021-10-01' and '2021-10-31'
    ),
    unioned as (
        (
            select
                city,
                org_time event_time,
                1 operation
            from
                record_in_range
        )
        union all
        (
            select
                city,
                term_time event_time,
                -1 operation
            from
                record_in_range
        )
    ),
    windowed as (
        select
            city,
            sum(operation) over (
                partition by
                    city
                order by
                    event_time,
                    operation desc
            ) wait_uv
        from
            unioned
    )
select
    city,
    max(wait_uv) max_wait_uv
from
    windowed
group by
    city
order by
    max_wait_uv,
    city;

with recursive date_range as (
  select 
    '2024-01-01' dt 
  union all 
  select 
    dt + interval 1 day 
  from 
    date_range 
  where 
    dt + interval 1 day < '2024-02-01'
), 
recursive time_range as (
  select 
    1 period 
  union all 
  select 
    period + 1 
  from 
    time_range 
  where 
    period + 1 < 97
) 
select 
  dt, 
  period 
from 
  date_range cross 
  join time_range 
order by 
  dt, 
  period;

select 
  user_id, 
  concat(
    upper(
      substring(name, 1, 1)
    ), 
    lower(
      substring(
        name 
        from 
          2
      )
    )
  ) name 
from 
  Users 
order by 
  user_id;

select 
  * 
from 
  Patients 
where 
  conditions like 'DIAB1%' 
  or conditions like '% DIAB1%';

select
    id,
    (
        length(string) - length(replace(string, ',', ''))
    ) cnt
from
    strings;

select 
  sell_date, 
  count(*) num_sold, 
  group_concat(
    product 
    order by 
      product asc separator ','
  ) products 
from 
  (
    select 
      distinct * 
    from 
      Activities
  ) sub 
group by 
  sell_date 
order by 
  sell_date;

select 
  * 
from 
  Users 
where 
  mail regexp '^[A-Za-z][A-Za-z0-9_.-]*@leetcode\\.com$';

with preprocess as (select distinct uid, date(in_time) date from tb_user_log where artical_id = 0 and sign_in = 1 and date(in_time) between '2021-07-07' and '2021-10-31'),
add_date_rnk as (select uid, date, row_number() over(partition by uid order by date) row_num from preprocess),
add_date_group as (select uid, date, (date - interval row_num day) date_sub_row from add_date_rnk),
add_consec_num as (select uid, date, row_number() over(partition by uid, date_sub_row order by date) consec_date from add_date_group),
add_prize as (select uid, date_format(date, '%Y%m') month, case consec_date % 7 when 3 then 3 when 0 then 7 else 1 end coin_this_day from add_consec_num)
select uid, month, sum(coin_this_day) coin from add_prize group by month, uid order by month, uid;

with signed_day as (select uid, date(in_time) dt, row_number() over(partition by uid order by date(in_time)) date_order from tb_user_log where artical_id = 0 and sign_in = 1 and date(in_time) between '2021-07-07' and '2021-10-31'),
add_date_group as (select uid, date_format(dt, '%Y%m') month, row_number() over(partition by uid, dt - interval date_order day order by dt) succ_day from signed_day)
select uid, month, sum(case succ_day % 7 when 3 then 3 when 0 then 7 else 1 end) coin from add_date_group group by uid, month order by month, uid;