您的位置:首页 > 科技 > IT业 > 网络托管运营_世界最大的购物网站_微信指数是搜索量吗_怎么在百度发布个人简介

网络托管运营_世界最大的购物网站_微信指数是搜索量吗_怎么在百度发布个人简介

2024/12/25 2:08:58 来源:https://blog.csdn.net/llvyeriji/article/details/142936368  浏览:    关键词:网络托管运营_世界最大的购物网站_微信指数是搜索量吗_怎么在百度发布个人简介
网络托管运营_世界最大的购物网站_微信指数是搜索量吗_怎么在百度发布个人简介

 刷题网站:

牛客网


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语句

selectdevice_id,gender,age,university,gpa
fromuser_profile
wheregpa > 3.5 and university = '山东大学'orgpa > 3.8 and university = '复旦大学'

%表示0个或多个字符 

selectdevice_id,age,university
fromuser_profile
whereuniversity like '%北京%'
selectmax(gpa)
fromuser_profile
whereuniversity = '复旦大学'
selectcount(gender) as male_num,avg(gpa) as avg_gpa
fromuser_profile
wheregender = 'male'

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

selectgender,university,count(*) as user_num,avg(active_days_within_30) as avg_active_day,avg(question_cnt) as avg_question_cnt
fromuser_profile
group bygender, university

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

selectdevice_id,age
fromuser_profile
order byage asc
selectdevice_id,gpa,age
fromuser_profile
order bygpa asc,age asc
selectdevice_id,gpa,age
fromuser_profile
order bygpa desc,age desc
selectcount(distinct device_id) as did_cnt,count(*) as question_cnt
fromquestion_practice_detail
wheredate like "2021-08%"
selectuniversity,round(avg(question_cnt),3) as avg_question_cnt,round(avg(answer_cnt),3) as avg_answer_cnt
fromuser_profile
group byuniversity
havingavg_question_cnt < 5oravg_answer_cnt < 20
selectuniversity,round(avg(question_cnt), 4) as avg_question_cnt
fromuser_profile
group byuniversity
order byavg_question_cnt asc
selectqd.device_id,qd.question_id,qd.result
fromquestion_practice_detail as qdinner joinuser_profile as upon qd.device_id = up.device_idandup.university = '浙江大学'
order bydevice_id

selectup.university,round(count(question_id)/count(distinct qpd.device_id),4) as avg_answer_cnt
fromquestion_practice_detail as qpdinner joinuser_profile as uponqpd.device_id = up.device_id
group byup.university
  • INNER JOIN:

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

    • 返回左表(第一个表)中的所有记录,即使在右表(第二个表)中没有匹配的记录。如果右表中没有匹配的行,结果集中对应的右表字段将显示为 NULL
selectue.university as university,ql.difficult_level as difficult_level,round(count(qpl.question_id)/count(distinct(qpl.device_id)),4) as avg_answer_cnt
fromquestion_practice_detail as qplleft joinuser_profile as ueon qpl.device_id = ue.device_idleft joinquestion_detail as qlon qpl.question_id = ql.question_id
group byue.university,ql.difficult_level
selectt1.university as university,t3.difficult_level as difficult_level,round(count(t2.question_id) / count(distinct (t2.device_id)),4) as avg_answer_cnt
fromuser_profile as t1,question_practice_detail as t2,question_detail as t3
wheret1.university = '山东大学'and t1.device_id = t2.device_idand t2.question_id = t3.question_id
group byt3.difficult_level

UNION vs UNION ALL

  • UNION:合并多个查询的结果集,自动去重,即如果两个查询的结果中有相同的行,最终结果集中只保留一行。
  • UNION ALL:合并多个查询的结果集,包括所有的行,不去重,即使有重复的行,也会全部显示。
selectcasewhen age<25 or age is null then "25岁以下"else "25岁及以上"end as age_cut,count(device_id) as number
fromuser_profile
group byage_cut
selectdevice_id,gender,casewhen age < 20 then '20岁以下'when age between 20 and 24  then '20-24岁'when age >= 25 then '25岁及以上'else '其他'end as age_cut
fromuser_profile;
selectDAY(date) as day,count(*) as question_cnt
fromquestion_practice_detail as ql
whereql.date like '2021-08-%'
group byql.date

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com