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
WITH user_login_days AS(
SELECT
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE login_time >= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
),
distinct_login_days AS (
SELECT
usr_id,
COUNT(DISTINCT login_date) AS login_days
FROM user_login_days
GROUP BY usr_id
)
SELECT
SUM(CASE WHEN login_days BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS days_1_to_5,
SUM(CASE WHEN login_days BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS day_6_to_10,
SUM(CASE WHEN login_days BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS days_11_to_20,
SUM(CASE WHEN login_days > 20 THEN 1 ELSE 0 END) AS days_over_20
FROM distinct_login_days
select
year(dt) AS Y
,CAST(AVG(CASE WHEN city = 'beijing' THEN tmp_h ELSE NULL END) AS decimal(4,2)) AS '北京'
,CAST(AVG(CASE WHEN city = 'shanghai' THEN tmp_h ELSE NULL END) AS decimal(4,2)) AS 上海
,CAST(AVG(CASE WHEN city = 'shenzhen' THEN tmp_h ELSE NULL END) AS decimal (4,2)) AS 深圳
,CAST(AVG(CASE WHEN city = 'guangzhou' THEN tmp_h ELSE NULL END) AS decimal (4,2)) AS 广州
from weather_rcd_china
WHERE year(dt) BETWEEN 2011 AND 2022
GROUP BY year(dt)
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 BYexam_date
select
year(dt) AS Y,
CAST(AVG(CASE WHEN city = 'beijing' THEN tmp_h ELSE NULL END) AS decimal(4,2)) AS '北京',
CAST(AVG(CASE WHEN city = 'shanghai' THEN tmp_h ELSE NULL END) AS decimal(4,2)) AS '上海',
CAST(AVG(CASE WHEN city = 'shenzhen' THEN tmp_h ELSE NULL END) AS decimal(4,2)) AS '深圳',
CAST(AVG(CASE WHEN city = 'guangzhou' THEN tmp_h ELSE NULL END) AS decimal (4,2)) AS '广州'
from weather_rcd_china
WHERE year(dt) BETWEEN 2011 AND 2022
GROUP BY year(dt)
select
s.singer_id,
s.singer_name,
a.album_id,
a.album_name,
COUNT(l.id) AS play_count
FROM singer_info s
JOIN album_info a USING(singer_id)
LEFT JOIN song_info si USING(album_id)
LEFT JOIN listen_rcd l USING(song_id)
GROUP BY s.singer_id, s.singer_name, a.album_id, a.album_name
HAVING play_count = 0
select
s.singer_id,
s.singer_name,
a.album_id,
a.album_name,
COUNT(l.id) AS play_count
FROM singer_info s
JOIN album_info a USING(singer_id)
LEFT JOIN song_info si USING(album_id)
LEFT JOIN listen_rcd l USING(song_id)
GROUP BY s.singer_id, s.singer_name, a.album_id, a.album_name
select
q.user_id,
DAYNAME(l.start_time) AS day_of_week,
COUNT(*) AS listen_per_day
from qqmusic_user_info q
JOIN listen_rcd l USING(user_id)
GROUP BY q.user_id, day_of_week
ORDER BY user_id, day_of_week
select
CASE
WHEN f.score >= 110 THEN '[110, 120]'
WHEN f.score >= 90 THEN '[90, 110)'
WHEN f.score >= 60 THEN '[60, 90)'
ELSE '[0, 60)'
END AS score_range,
COUNT(*) AS num_students
from students s
JOIN scores f USING(student_id)
WHERE f.exam_date = '2024-06-30'
AND f.subject = '数学'
GROUP BY score_range
ORDER BY score_range DESC
select
r.prd_id,
m.prd_nm,
COUNT(*) AS exposure_count
from tb_pg_act_rcd r
JOIN tb_prd_map m USING(prd_id)
GROUP BY r.prd_id, m.prd_nm
ORDER BY exposure_count desc
LIMIT 1
select
d.cust_uid,
d.start_loc,
d.end_loc,
d.start_tm,
d.car_cls
from didi_sht_rcd d
JOIN loc_nm_ctg l
ON d.end_loc = l.loc_nm
WHERE l.loc_ctg = '餐饮'
ORDER BY d.start_tm
select
d.cust_uid,
d.start_loc,
d.end_loc,
d.start_tm,
d.car_cls
from didi_sht_rcd d
JOIN loc_nm_ctg l
ON d.end_loc = l.loc_nm
WHERE d.car_cls = 'A' OR d.car_cls = 'C'
ORDER BY d.start_tm
select
t1.live_id,
t2.live_nm,
COUNT(*) AS enter_cnt
from ks_live_t1 t1
JOIN ks_live_t2 t2 USING(live_id)
WHERE DATE_FORMAT(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
k1.live_id,
k2.live_nm,
COUNT(*) AS enter_cnt
from ks_live_t1 k1
JOIN ks_live_t2 k2 USING(live_id)
WHERE DATE_FORMAT(k1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
GROUP BY k1.live_id,
k2.live_nm
ORDER BY enter_cnt desc
LIMIT 5
select
year(dt) AS Y
,CAST(AVG(CASE WHEN city = 'beijing' THEN tmp_h ELSE NULL END) AS decimal(4,2)) AS '北京'
,CAST(AVG(CASE WHEN city = 'shanghai' THEN tmp_h ELSE NULL END) AS decimal(4,2)) AS '上海'
,CAST(AVG(CASE WHEN city = 'shenzhen' THEN tmp_h ELSE NULL END) AS decimal(4,2)) AS '深圳'
,CAST(AVG(CASE WHEN city = 'guangzhou' THEN tmp_h ELSE NULL END) AS decimal(4,2)) AS '广州'
from weather_rcd_china
WHERE year(dt) BETWEEN 2011 AND 2022
GROUP BY year(dt)