select city,
sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days
,concat(cast(sum(case when con like '%多云%' then 1 else 0 end)/count(1)*100 as decimal(10,2)),'%') as p
from
weather_rcd_china
where
year(dt)=2021
group by
city
order by
3 desc 而且也能通过
round和cast as decimal是有区别的这个你知道不？ round(23.657,2)=23.66, decimal的话等于23.65。
select city,
sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days
,concat(cast(sum(case when con like '%多云%' then 1 else 0 end)/count(1)*100 as decimal(10,2)),'%') as p
from
weather_rcd_china
where
year(dt)=2021
group by
city
order by
3 desc 而且也能通过
如下代码可以通过测试呀，你再试试呢。 SELECT
t2.live_id,
t2.live_nm,
COUNT(*) AS enter_cnt
FROM
ks_live_t1 t1
JOIN
ks_live_t2 t2
ON
t1.live_id = t2.live_id
WHERE
DATE_FORMAT(t1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
GROUP BY
t1.live_id, t2.live_nm
ORDER BY
enter_cnt DESC
LIMIT 5;
SELECT *
FROM numbers_for_fun
WHERE
(a = 0 AND (
(b = 0 AND c < 0)
OR (b != 0 AND (
(b > 0 AND c < 0)
OR (b < 0)
))
))
OR
(a != 0 AND (
(a > 0 AND (c < 0 OR (b < 0 AND 4*a*c < b*b)))
OR
(a < 0)
));
WITH score_ranges AS (
SELECT
s.class_code,
CASE
WHEN sc.score >= 110 THEN 'excellent'
WHEN sc.score >= 90 THEN 'good'
WHEN sc.score >= 60 THEN 'pass'
ELSE 'fail'
END AS score_range
FROM
students s
JOIN
scores sc ON s.student_id = sc.student_id
WHERE
sc.subject = '数学'
AND sc.exam_date = '2024-06-30'
),
count_score AS (
SELECT
sr.class_code,
SUM(CASE WHEN sr.score_range = 'excellent' THEN 1 ELSE 0 END) AS excellent_count,
SUM(CASE WHEN sr.score_range = 'good' THEN 1 ELSE 0 END) AS good_count,
SUM(CASE WHEN sr.score_range = 'pass' THEN 1 ELSE 0 END) AS pass_count,
SUM(CASE WHEN sr.score_range = 'fail' THEN 1 ELSE 0 END) AS fail_count,
COUNT(*) AS total_students
FROM
score_ranges sr
GROUP BY
sr.class_code
)
SELECT
class_code,
total_students,
CONCAT(excellent_count,', ',ROUND(excellent_count / total_students * 100, 2), '%') AS excellent_rate,
CONCAT(good_count,', ',ROUND(good_count / total_students * 100, 2), '%') AS good_rate,
CONCAT(pass_count,', ',ROUND(pass_count / total_students * 100, 2), '%') AS pass_rate,
CONCAT(fail_count,', ',ROUND(fail_count / total_students * 100, 2), '%') AS fail_rate
FROM
count_score
ORDER BY
class_code;
WITH score_ranges AS (
SELECT
s.class_code,
CASE
WHEN sc.score >= 110 THEN 'excellent'
WHEN sc.score >= 90 THEN 'good'
WHEN sc.score >= 60 THEN 'pass'
ELSE 'fail'
END AS score_range
FROM
students s
JOIN
scores sc ON s.student_id = sc.student_id
WHERE
sc.subject = '数学'
AND sc.exam_date = '2024-06-30'
),
count_score AS (
SELECT
sr.class_code,
SUM(CASE WHEN sr.score_range = 'excellent' THEN 1 ELSE 0 END) AS excellent_count,
SUM(CASE WHEN sr.score_range = 'good' THEN 1 ELSE 0 END) AS good_count,
SUM(CASE WHEN sr.score_range = 'pass' THEN 1 ELSE 0 END) AS pass_count,
SUM(CASE WHEN sr.score_range = 'fail' THEN 1 ELSE 0 END) AS fail_count,
COUNT(*) AS total_students
FROM
score_ranges sr
GROUP BY
sr.class_code
)
SELECT
class_code,
total_students,
CONCAT(excellent_count,',',ROUND(excellent_count / total_students * 100, 2), '%') AS excellent_rate,
CONCAT(good_count,',',ROUND(good_count / total_students * 100, 2), '%') AS good_rate,
CONCAT(pass_count,',',ROUND(pass_count / total_students * 100, 2), '%') AS pass_rate,
CONCAT(fail_count,',',ROUND(fail_count / total_students * 100, 2), '%') AS fail_rate
FROM
count_score
ORDER BY
class_code;
WITH score_ranges AS (
SELECT
s.class_code,
CASE
WHEN sc.score >= 110 THEN 'excellent'
WHEN sc.score >= 90 THEN 'good'
WHEN sc.score >= 60 THEN 'pass'
ELSE 'fail'
END AS score_range
FROM
students s
JOIN
scores sc ON s.student_id = sc.student_id
WHERE
sc.subject = '数学'
AND sc.exam_date = '2024-06-30'
),
class_totals AS (
SELECT
class_code,
COUNT(*) AS total_students
FROM
score_ranges
GROUP BY
class_code
)
SELECT
sr.class_code,
total_students,
CONCAT(SUM(CASE WHEN sr.score_range = 'excellent' THEN 1 ELSE 0 END), ', ',
ROUND(100.0 * SUM(CASE WHEN sr.score_range = 'excellent' THEN 1 ELSE 0 END) / ct.total_students, 2), '%') AS excellent,
CONCAT(SUM(CASE WHEN sr.score_range = 'good' THEN 1 ELSE 0 END), ', ',
ROUND(100.0 * SUM(CASE WHEN sr.score_range = 'good' THEN 1 ELSE 0 END) / ct.total_students, 2), '%') AS good,
CONCAT(SUM(CASE WHEN sr.score_range = 'pass' THEN 1 ELSE 0 END), ', ',
ROUND(100.0 * SUM(CASE WHEN sr.score_range = 'pass' THEN 1 ELSE 0 END) / ct.total_students, 2), '%') AS pass,
CONCAT(SUM(CASE WHEN sr.score_range = 'fail' THEN 1 ELSE 0 END), ', ',
ROUND(100.0 * SUM(CASE WHEN sr.score_range = 'fail' THEN 1 ELSE 0 END) / ct.total_students, 2), '%') AS fail
FROM
score_ranges sr
JOIN
class_totals ct ON sr.class_code = ct.class_code
GROUP BY
sr.class_code, ct.total_students
ORDER BY
sr.class_code;
select
trx_amt,
count(1) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
count(1) / count(distinct usr_id) as avg_trx_per_user
from
cmb_usr_trx_rcd
where
mch_nm = '红玫瑰按摩保健休闲'
and
(
(year(trx_time) = 2023 and month(trx_time) between 1 and 12)
or (year(trx_time) = 2024 and month(trx_time) between 1 and 6)
)
group by
trx_amt
order by
avg_trx_per_user desc
limit 5;
with daily_sales as (
select
date(order_time) as order_date,
goods_id,
sum(order_gmv) as total_gmv
from
order_info
where
date(order_time) >= '2024-10-01' and date(order_time) < '2024-11-01'
group by
date(order_time), goods_id
),
ranked_sales as (
select
order_date,
goods_id,
total_gmv,
row_number() over (partition by order_date order by total_gmv asc) as ranking
from
daily_sales
)
select
order_date,
goods_id,
total_gmv,
ranking
from
ranked_sales
where
ranking <= 3
order by
order_date,
ranking;
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;
select student_id,max(score) max_score, min(score) min_score, avg(score) avg_score
from scores
group by student_id
having min(score)>=80
order by student_id
select city,
sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days
,concat(cast(sum(case when con like '%多云%' then 1 else 0 end)/count(1)*100 as decimal(10,2)),'%') as p
from
weather_rcd_china
where
year(dt)=2021
group by
city
order by
3 desc
select
case when trx_amt=288 then '1.WithHand'
when trx_amt=388 then '2.WithMimi'
when trx_amt=588 then '3.BlowJobbie'
when trx_amt=888 then '4.Doi'
when trx_amt=1288 then '5.DoubleFly'
else '6.other'
end as ser_typ
,count(1) as trx_cnt
,min(date(trx_time)) as first_date
from
cmb_usr_trx_rcd
where usr_id='5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by ser_typ
select
usr_id
,mch_nm
,sum(trx_amt) as trx_amt
,count(1) as trx_cnt
,min(trx_time) as first_time
from
cmb_usr_trx_rcd
where
usr_id='5201314520'
and trx_amt>=288
group by usr_id, mch_nm
order by 4 desc
同学，两个点说明了你的基础很薄弱哈。 1.请问date('2024-09-30 12:23:12')输出的日期是什么？你写成了betwwen 的尾巴'2024-10-01' ，那会把这天的数据也包括进去的； 2.and 和or区分好，只要学过初中英语你就能会啊！