select s.student_id,
s.name,
c.score,
row_number()over() as rnk
from students s
left join scores c
on s.student_id=c.student_id
where s.grade_code="S1"
and c.subject="物理"
order by c.score desc
limit 10
这样符合题目要求吗
select city,
sum(case when con like "%多云%" then 1 else 0 end) as cloudy_days,
concat(round(100*sum(case when con like "%多云%" then 1 else 0 end)/count(1) ,2) ,"%") as p
from weather_rcd_china
where left(dt,4)=2021
group by city
order by 3 desc
救命,本题的正确写法是什么,怎么用round可以,cast decimal就不行呢
round和cast as decimal是有区别的这个你知道不? round(23.657,2)=23.66, decimal的话等于23.65。
select user_id,
date_format(start_time,"%W") as day_of_week,
count(if_finished) as listens_per_day
from listen_rcd
group by user_id,date_format(start_time,"%W")
order by user_id,date_format(start_time,"%W")
select user_id,
date_format(start_time,"%W") as day_of_week,
count(if_finished) as listens_per_day
from listen_rcd
group by user_id,date_format(start_time,"%W")
select *
from tx_red_pkt_rcd
where snd_usr_id in
(
select snd_usr_id
from tx_red_pkt_rcd
where pkt_amt in(200,520)
group by snd_usr_id
having count(case when pkt_amt in(200,520)then 1end)>=5
)
order by snd_usr_id,snd_datetime
select *
from tx_red_pkt_rcd
where snd_usr_id in
(
select snd_usr_id
from tx_red_pkt_rcd
where pkt_amt in(200,520)
group by snd_usr_id
having count(case when pkt_amt in(200,520)then 1end)>=5
)
and rcv_datetime>=snd_datetime
order by snd_usr_id,snd_datetime
select *
from tx_red_pkt_rcd
where snd_usr_id in
(
select snd_usr_id
from tx_red_pkt_rcd
where pkt_amt in(200,520)
group by snd_usr_id
having count(case when pkt_amt in(200,520)then 1end)>=5
)
and rcv_datetime>=snd_datetime
select *
from tx_red_pkt_rcd
where snd_usr_id in
(
select snd_usr_id
from tx_red_pkt_rcd
group by snd_usr_id
having (count(snd_usr_id)>=5 and (pkt_amt>=520 or pkt_amt>=200))
)
and rcv_datetime>=snd_datetime
select *
from cmb_usr_trx_rcd
where usr_id="5201314520"
and (date(trx_time) between "2024-06-08" and "2024-06-10"
or date(trx_time) between "2024-09-15" and "2024-09-17")
order by trx_time
SELECT
exam_date,
MAX(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS chinese_score,
MAX(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS math_score,
MAX(CASE WHEN subject = '英语' THEN score ELSE NULL END) AS english_score
FROM scores
WHERE student_id = 460093 AND subject IN ('语文', '数学', '英语')
GROUP BY exam_date
ORDER BY exam_date;
select
exam_date,
case when subject="语文" then score else null end as "chinese_score",
case when subject="数学" then score else null end as "math_score",
case when subject="英语" then score else null end as "english_score"
from scores
where student_id="460093"
and subject in ("语文","数学","英语")
select
student_id,
sum(score) as total_score
from scores
where subject in ("语文","数学","英语")
and exam_date="2024-06-30"
group by student_id
having sum(score)>330