sql常用操作之高级条件语句

题1

https://www.nowcoder.com/practice/2ed07ff8f67a474d90523b88402e401b?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select 
    uid,
    nick_name,
    achievement 
FROM    
    user_info left join exam_record using(uid) # user_info作主表
    left join practice_record as p_r using(uid)
where 
    achievement between 1200 and 2500
    and nick_name like "牛客%号"
    and (start_time like "_____09%" or month(p_r.submit_time)=9 )
group by 
    uid

select uid,
       nick_name,
       achievement
from user_info
where nick_name like '牛客%号'
and achievement between 1200 and 2500
and (
    uid in(
        select uid
        from exam_record
        where date_format(submit_time, '%Y%m') = '202109')
    or uid in(
        select uid
        from practice_record
        where  date_format(submit_time, '%Y%m') = '202109')
)

题2

https://www.nowcoder.com/practice/1c5075503ccf4de1882976b2fff2c072?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select 
    uid,
    exam_id,
    round(avg(score), 0) as avg_score
from 
    user_info left join exam_record using(uid)
    left join examination_info  using(exam_id)
where 
    (nick_name like "牛客%号" or nick_name rlike "^[0-9]+$" )
    and tag rlike "^(c|C).*"
    and score is not null
group BY    
    uid,exam_id
order by uid,avg_score

select uid, exam_id, round(avg(score), 0) as avg_score
from exam_record
where 
    uid in (select uid from user_info
               # 正则表达式
              where nick_name rlike '^牛客[0-9]+号$'
              or nick_name rlike '^[0-9]+$'
             )
    and exam_id in (select exam_id
                from examination_info
                # 通配符
                where tag like 'C%'
                or tag like 'c%'
               )
    and score IS NOT NULL
group by uid, exam_id
order by uid, avg_score;

select uid, exam_id, round(avg(score), 0) as avg_score
from exam_record
group by uid, exam_id
having 
    uid in (select uid from user_info
               # 正则表达式
              where nick_name rlike '^牛客[0-9]+号$'
              or nick_name rlike '^[0-9]+$'
             )
    and exam_id in (select exam_id
                from examination_info
                # 通配符
                where tag like 'C%'
                or tag like 'c%'
               )
    and avg_score IS NOT NULL #  注意这里若是score is not null，则报"Unknown column 'score' in 'having clause'"
order by uid, avg_score;

题3

https://www.nowcoder.com/practice/f72d3fc27dc14f3aae76ee9823ccca6b?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

with…as创建临时表方式

WITH t_tag_count as (
    SELECT uid, `level`,
        COUNT(start_time) - COUNT(submit_time) as incomplete_cnt, -- 未完成数
        ROUND(IFNULL(1 - COUNT(submit_time) / COUNT(start_time), 0), 3) as incomplete_rate, -- 此人未完成率
        COUNT(start_time) as total_cnt -- 总作答数
#     FROM exam_record RIGHT JOIN user_info USING(uid) 
    FROM user_info LEFT JOIN exam_record USING(uid) #user_info 作主表
    GROUP BY uid
)

SELECT uid, incomplete_cnt, incomplete_rate
FROM t_tag_count
WHERE EXISTS ( 
    SELECT uid FROM t_tag_count WHERE `level` = 0 AND incomplete_cnt > 2 ) AND `level` = 0 

UNION ALL

SELECT uid, incomplete_cnt, incomplete_rate
FROM t_tag_count
WHERE NOT EXISTS (
    SELECT uid FROM t_tag_count WHERE `level` = 0 AND incomplete_cnt > 2 ) AND total_cnt > 0
    
ORDER BY incomplete_rate;

子表方式：

SELECT uid, incomplete_cnt, incomplete_rate
FROM (
    select 
        ui.uid uid, level,     ###一定要是ui.uid 才能和level一一对应
        count(start_time)-count(submit_time)  incomplete_cnt,
        round(ifnull((count(start_time)-count(submit_time))/count(start_time),0),3)  incomplete_rate,
        COUNT(start_time) as total_cnt
    from exam_record er
        right join user_info ui on er.uid=ui.uid
    group by uid
    ) as a
WHERE EXISTS (           #判断为 有任意一个0级用户未完成试卷数大于2
    SELECT uid 
    FROM (
        select 
            ui.uid uid, level,     ###一定要是ui.uid 才能和level一一对应
            count(start_time)-count(submit_time)  incomplete_cnt,
            round(ifnull((count(start_time)-count(submit_time))/count(start_time),0),3)  incomplete_rate,
            COUNT(start_time) as total_cnt
        from exam_record er
        right join user_info ui on er.uid=ui.uid
        group by uid
        ) as a
    WHERE `level` = 0 AND incomplete_cnt > 2 )
AND level = 0    #输出0级用户的未完成数和未完成率

union all        ##两种情况用union all 连接

SELECT uid, incomplete_cnt, incomplete_rate
FROM (
    select 
        ui.uid uid, level,     ###一定要是ui.uid 才能和level一一对应
        count(start_time)-count(submit_time)  incomplete_cnt,
        round(ifnull((count(start_time)-count(submit_time))/count(start_time),0),3)  incomplete_rate,
        COUNT(start_time) as total_cnt
    from exam_record er
    right join user_info ui on er.uid=ui.uid
    group by uid
    ) a
WHERE not EXISTS (       #判断为 没有任意一个0级用户未完成试卷数大于2
    SELECT uid 
    FROM (
        select 
            ui.uid uid, level,     ###一定要是ui.uid 才能和level一一对应
            count(start_time)-count(submit_time)  incomplete_cnt,
            round(ifnull((count(start_time)-count(submit_time))/count(start_time),0),3)  incomplete_rate,
            COUNT(start_time) as total_cnt
        from exam_record er
        right join user_info ui on er.uid=ui.uid
        group by uid
        ) a
    WHERE `level` = 0 AND incomplete_cnt > 2 )
AND total_cnt >0                      #筛选有作答记录的用户 即总作答数大于0即可
order by incomplete_rate asc

其它：

WITH target_user AS (
    SELECT 
        user_info.uid, 
        COUNT(1) AS incomplete_cnt
    FROM exam_record LEFT JOIN user_info ON exam_record.uid = user_info.uid
    WHERE user_info.level = 0 AND submit_time IS NULL
    GROUP BY user_info.uid
    HAVING incomplete_cnt > 2
), target_user_exist AS (
SELECT COUNT(1) AS `exist` FROM target_user)
, total_summary AS (
        SELECT
            user_info.uid,
            MAX(user_info.level) AS level,
            SUM(IF(submit_time IS NULL AND start_time IS NOT NULL, 1, 0)) AS incomplete_cnt,
            SUM(IF(submit_time IS NULL AND start_time IS NOT NULL, 1, 0)) / COUNT(1) AS incomplete_rate,
            SUM(IF(start_time IS NOT NULL, 1, 0)) AS has_submit
        FROM user_info LEFT JOIN exam_record
        ON user_info.uid = exam_record.uid
        GROUP BY user_info.uid
)

SELECT
    uid,
    incomplete_cnt,
    ROUND(incomplete_rate, 3)
FROM total_summary LEFT JOIN target_user_exist ON 1=1
WHERE (exist=0 AND has_submit>0) OR (exist=1 AND level=0)
ORDER BY incomplete_rate ASC

题4

https://www.nowcoder.com/practice/ebff819fd38c46db8a42dfe43ca7b33a?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
本题关于case…when…then…[else]…end有多种写法：

(case when score<60 then '差'
     when score<75 then '中'
     when score<90 then '良'
     else '优' end) as score_grade
         
(case when score >= 90 then '优'
      when score >= 75 then '良'
      when score >= 60 then '中'
      else '差' end) as score_grade

(case when score>=90 then "优"
      when score between 75 and 89 then "良"
      when score between 60 and 74 then "中"
      else "差" end) as score_grade      

(case when score>=90 then '优'
      when score>=75 and score<90 then '良'
      when score>=60 and score<75 then '中'
      else '差' end) as score_grade

法1：

select
    level, score_grade, 
    round(count(uid) / total, 3) as ratio
from (
    select u_i.uid,
           exam_id, score, level,
           (case when score >= 90 then '优'
                when score >= 75 then '良'
                when score >= 60 then '中'
                else '差' end) as score_grade,
           count(*) over(partition by level) as total
    from user_info u_i join exam_record e_r on u_i.uid = e_r.uid
    where score is not null 
    ) as  user_grade_table
group by level, score_grade
order by level desc, ratio desc

法2：

with t1 as
    (select level,
         case when score<60 then '差'
              when score<75 then '中'
              when score<90 then '良'
         else '优' end as score_grade
    from exam_record join user_info on exam_record.uid=user_info.uid
    where score is not null)
    
select t1.level, score_grade, round(count(score_grade) / ct,3) as cnt
from t1 join (select level,count(level) ct
              from t1
              group by level) as t2
on t1.level=t2.level
group by t1.level,score_grade
order by t1.level desc,cnt desc;

上面方法的改写：

with t1 as
    (select level,
         case when score<60 then '差'
              when score<75 then '中'
              when score<90 then '良'
         else '优' end as score_grade
    from exam_record join user_info on exam_record.uid=user_info.uid
    where score is not null),
t2 as 
    (select level,count(level) as ct
     from t1
     group by level)
    
select t1.level, score_grade, round(count(score_grade) / ct,3) as cnt
from t1 join t2
on t1.level=t2.level
group by t1.level,score_grade
order by t1.level desc,cnt desc;

法3：

with t as 
    (select u_i.uid,exam_id,score,level,
           case when score>=90 then '优' 
                when score>=75 and score<90 then '良'
                when score>=60 and score<75 then '中'
                else '差' end as score_grade,
           count(*) over (partition by level) as total
    from user_info u_i join exam_record  using(uid)
    where score is not NULL)
    
select 
    level,
    score_grade,
    round(count(*) / total,3) as ratio
from t
group by level, score_grade
order by level desc, ratio desc