2024.10.15 sql

?刷题网站:

牛客网


select device_id as user_infos_example
from user_profile where id <= 2
select device_id, university from user_profile
where university="北京大学"
select device_id, gender, age, university
from user_profile
where age > 24

between and 是左右闭区间[]?

select device_id, gender, age
from user_profile
where age between 20 and 23

单引号!?

select device_id, gender, age, university 
from user_profile
where university!='复旦大学'

格式化sql语句

select
    device_id,
    gender,
    age,
    university,
    gpa
from
    user_profile
where
    gpa > 3.5 and university = '山东大学'
    or
    gpa > 3.8 and university = '复旦大学'

%表示0个或多个字符?

select
    device_id,
    age,
    university
from
    user_profile
where
    university like '%北京%'
select
    max(gpa)
from
    user_profile
where
    university = '复旦大学'
select
    count(gender) as male_num,
    avg(gpa) as avg_gpa
from
    user_profile
where
    gender = 'male'

有没有人管管牛客网,怎么代码一模一样的,第一次对,第二次不多ToT!

select
    gender,
    university,
    count(*) as user_num,
    avg(active_days_within_30) as avg_active_day,
    avg(question_cnt) as avg_question_cnt
from
    user_profile
group by
    gender, university

加round()默认四舍五入到小数点后一位

select
    device_id,
    age
from
    user_profile
order by
    age asc
select
    device_id,
    gpa,
    age
from
    user_profile
order by
    gpa asc,
    age asc
select
    device_id,
    gpa,
    age
from
    user_profile
order by
    gpa desc,
    age desc
select
    count(distinct device_id) as did_cnt,
    count(*) as question_cnt
from
    question_practice_detail
where
    date like "2021-08%"
select
    university,
    round(avg(question_cnt),3) as avg_question_cnt,
    round(avg(answer_cnt),3) as avg_answer_cnt
from
    user_profile
group by
    university
having
    avg_question_cnt < 5
    or
    avg_answer_cnt < 20
select
    university,
    round(avg(question_cnt), 4) as avg_question_cnt
from
    user_profile
group by
    university
order by
    avg_question_cnt asc
select
    qd.device_id,
    qd.question_id,
    qd.result
from
    question_practice_detail as qd
    inner join
    user_profile as up
    on 
    qd.device_id = up.device_id
    and
    up.university = '浙江大学'
order by
    device_id

2024.10.15 sql

select
    up.university,
    round(count(question_id)/count(distinct qpd.device_id),4) as avg_answer_cnt
from
    question_practice_detail as qpd
    inner join
    user_profile as up
    on
    qpd.device_id = up.device_id
group by
    up.university
  • INNER JOIN:

    • 只返回两个表中匹配的记录。当连接的表中没有匹配的行时,这些行将不会出现在结果集中。
  • LEFT JOIN (或 LEFT OUTER JOIN):

    • 返回左表(第一个表)中的所有记录,即使在右表(第二个表)中没有匹配的记录。如果右表中没有匹配的行,结果集中对应的右表字段将显示为?NULL
select
    ue.university as university,
    ql.difficult_level as difficult_level,
    round(count(qpl.question_id)/count(distinct(qpl.device_id)),4) as avg_answer_cnt
from
    question_practice_detail as qpl
    left join
    user_profile as ue
    on qpl.device_id = ue.device_id
    left join
    question_detail as ql
    on 
    qpl.question_id = ql.question_id
group by
    ue.university,ql.difficult_level
select
    t1.university as university,
    t3.difficult_level as difficult_level,
    round(
        count(t2.question_id) / count(distinct (t2.device_id)),
        4
    ) as avg_answer_cnt
from
    user_profile as t1,
    question_practice_detail as t2,
    question_detail as t3
where
    t1.university = '山东大学'
    and t1.device_id = t2.device_id
    and t2.question_id = t3.question_id
group by
    t3.difficult_level

UNION?vs?UNION ALL

  • UNION:合并多个查询的结果集,自动去重,即如果两个查询的结果中有相同的行,最终结果集中只保留一行。
  • UNION ALL:合并多个查询的结果集,包括所有的行,不去重,即使有重复的行,也会全部显示。
select
    case
        when age<25 or age is null then "25岁以下"
        else "25岁及以上"
    end as age_cut,
    count(device_id) as number
from
    user_profile
group by
    age_cut
select
    device_id,
    gender,
    case
        when age < 20 then '20岁以下'
        when age between 20 and 24  then '20-24岁'
        when age >= 25 then '25岁及以上'
        else '其他'
    end as age_cut
from
    user_profile;
select
    DAY(date) as day,
    count(*) as question_cnt
from
    question_practice_detail as ql
where
    ql.date like '2021-08-%'
group by
    ql.date
上一篇:ipad4升级ios11教程(为什么我Ipad4更新不了IOS11系统)
下一篇:C语言 | Leetcode C语言题解之第419题棋盘上的战舰