SELECT
m.mch_typ,
COUNT(u.trx_amt) AS trx_cnt,
SUM(u.trx_amt) AS trx_amt
FROM
cmb_usr_trx_rcd u
LEFT JOIN
cmb_mch_typ m ON u.mch_nm = m.mch_nm
WHERE
u.usr_id = '5201314520' and left(trx_time,4)=2024
GROUP BY
m.mch_typ
ORDER BY
2 DESC
select
mch_typ,
count(trx_time) as trx_cnt,
sum(trx_amt) as trx_amt
from
cmb_usr_trx_rcdrcd
join
cmb_mch_typ typ onrcd.mch_nm = typ.mch_nm
where
usr_id = '5201314520'
and trx_time like '2024%'
group by
mch_typ
order by
trx_cnt desc;
with route as(
select
rcd.start_loc as startplot,
rcd.end_loc as endplot
from
didi_sht_rcd rcd
join
loc_nm_ctg ctgstart on rcd.start_loc = ctgstart.loc_nm
join
loc_nm_ctg ctgend on rcd.end_loc = ctgend.loc_nm
where
ctgstart.loc_ctg = '酒店'
and
ctgend.loc_ctg = '餐饮'
)
select
startplot,
endplot,
count(*) as times
from
route
group by
startplot, endplot
order by
times desc
limit1;
SELECT
u.user_id,
DAYNAME(lr.start_time) AS day_of_week,
COUNT(*) AS listens_per_day
FROM
qqmusic_user_info u
JOIN
listen_rcd lr ON u.user_id = lr.user_id
GROUP BY
u.user_id, day_of_week
ORDER BY
u.user_id ASC, day_of_week ASC;
select
brnd,
keyword
from(
select
gd.brnd_nm as brnd,
query.key_wordas keyword,
row_number()over(partition by gd.brnd_nm order by count(query.key_word) desc) as rk
from
jx_pchs_rcd pchs
join
jx_gd_page_map gd on pchs.good_id = gd.gd_id
left join
jx_click_rcd click onpchs.session_id = click.session_id
left join
jx_query_rcd query on pchs.session_id = query.session_id
group by
gd.brnd_nm, query.key_word
) as subquery
where
rk <= 3
and keyword is not null
order by
brnd asc , keyword
select
map.prd_id,
map.prd_nm,
count(map.prd_nm) as sndnum
from
tb_pg_act_rcd rcd
join
tb_prd_map map on rcd.prd_id = map.prd_id
group by
rcd.prd_id,map.prd_nm
order by
sndnum desc
limit
1
WITH hotel_routes AS (
SELECT r.start_loc, r.end_loc, l_end.loc_ctg, COUNT(*) AS trip_count
FROM didi_sht_rcd r
JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
WHERE l_start.loc_ctg = '酒店'
GROUP BY r.start_loc, r.end_loc, l_end.loc_ctg
),
ranked_routes AS (
SELECT start_loc, end_loc, loc_ctg, trip_count,
ROW_NUMBER() OVER (PARTITION BY loc_ctg ORDER BY trip_count DESC) AS route_rank
FROM hotel_routes
)
SELECT start_loc, end_loc, loc_ctg, trip_count
FROM ranked_routes
WHERE route_rank = 1
ORDER BY trip_count DESC;
SELECT
SL,
EL,
TYPE,
NUM
FROM
(SELECT
r.start_loc AS SL,
r.end_loc AS EL,
COUNT(*) AS NUM,
l_end.loc_ctg AS TYPE,
ROW_NUMBER() OVER(PARTITION BY l_end.loc_ctg ORDER BY COUNT(*) DESC) AS RK
FROM
didi_sht_rcd r
JOIN
loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
JOIN
loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
WHERE
l_start.loc_ctg = '酒店'
AND l_end.loc_ctg <> '酒店'
GROUP BY
r.start_loc, r.end_loc, l_end.loc_ctg, l_end.loc_nm
) AS SUBQUERY
WHERE
RK = 1
order by
NUM DESC
SELECT
SL,
EL,
TYPE,
NUM
FROM
(SELECT
r.start_loc AS SL,
r.end_loc AS EL,
COUNT(*) AS NUM,
l_end.loc_ctg AS TYPE,
ROW_NUMBER() OVER(PARTITION BY l_end.loc_nm ORDER BY COUNT(*) DESC) AS RK
FROM
didi_sht_rcd r
JOIN
loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
JOIN
loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
WHERE
l_start.loc_ctg = '酒店'
AND l_end.loc_ctg <> '酒店'
GROUP BY
r.start_loc, r.end_loc, l_end.loc_ctg, l_end.loc_nm
) AS SUBQUERY
WHERE
RK = 1
order by
NUM DESC
SELECT
SL,
EL,
TYPE,
NUM
FROM
(SELECT
r.start_loc AS SL,
r.end_loc AS EL,
COUNT(*) AS NUM,
l_end.loc_ctg AS TYPE,
ROW_NUMBER() OVER(PARTITION BY l_end.loc_nm ORDER BY COUNT(*) DESC) AS RK
FROM
didi_sht_rcd r
JOIN
loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
JOIN
loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
WHERE
l_start.loc_ctg = '酒店'
GROUP BY
r.start_loc, r.end_loc, l_end.loc_ctg, l_end.loc_nm
) AS SUBQUERY
WHERE
RK = 1
order by
NUM DESC
SELECT r.*
FROM didi_sht_rcd r
JOIN loc_nm_ctg l1 ON r.start_loc = l1.loc_nm
JOIN loc_nm_ctg l2 ON r.end_loc = l2.loc_nm
WHERE l1.loc_ctg = '住宅'
AND l2.loc_ctg = '写字楼'
ORDER BY r.start_tm ASC;
SELECT r.*
FROM didi_sht_rcd r
JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
WHERE l_start.loc_ctg = '餐饮'
AND l_end.loc_ctg = '餐饮'
ORDER BY r.start_tm ASC;
select
k1.live_id,
k2.live_nm,
count(k1.usr_id) as usrnum
from
ks_live_t1 k1
join
ks_live_t2 k2 on k2.live_id = k1.live_id
where
DATE_FORMAT(k1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
group by
live_id,live_nm
order by
usrnum desc
limit
5;
select
count(*) as '总行数',
count(distinct student_id) as '不同学生的数量',
sum(case when score is not null then 1 else 0 end) as '成绩不为空的行数',
sum(case when score = '0' then 1 else 0 end) as '成绩为0的行数',
sum(case when score isnull then 1 else 0 end) as '成绩为空的行数'
from
scores
WITH class_stats AS (
SELECT
s.class_code AS class,
COUNT(DISTINCT s.student_id) AS total_student,
SUM(CASE WHEN sc.score >= 110 THEN 1 ELSE 0 END) AS excellent_count,
SUM(CASE WHEN sc.score >= 90 AND sc.score < 110 THEN 1 ELSE 0 END) AS good_count,
SUM(CASE WHEN sc.score >= 60 AND sc.score < 90 THEN 1 ELSE 0 END) AS pass_count,
SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END) AS fail_count
FROM
students s
JOIN
scores sc ON s.student_id = sc.student_id
WHERE
sc.subject = '数学'
AND sc.exam_date = '2024-6-30'
GROUP BY
s.class_code
)
SELECT
class,
total_student,
CONCAT(
excellent_count,
' ,',
ROUND(excellent_count * 100.0 / NULLIF(total_student, 0), 2),
'%'
) AS excellent,
CONCAT(
good_count,
' ,',
ROUND(good_count * 100.0 / NULLIF(total_student, 0), 2),
'%'
) AS good,
CONCAT(
pass_count,
' ,',
ROUND(pass_count * 100.0 / NULLIF(total_student, 0), 2),
'%'
) AS pass,
CONCAT(
fail_count,
' ,',
ROUND(fail_count * 100.0 / NULLIF(total_student, 0), 2),
'%'
) AS fail
FROM
class_stats;
WITH class_stats AS (
SELECT
s.class_code AS class,
COUNT(DISTINCT s.student_id) AS total_student,
SUM(CASE WHEN sc.score >= 110 THEN 1 ELSE 0 END) AS excellent_count,
SUM(CASE WHEN sc.score >= 90 AND sc.score < 110 THEN 1 ELSE 0 END) AS good_count,
SUM(CASE WHEN sc.score >= 60 AND sc.score < 90 THEN 1 ELSE 0 END) AS pass_count,
SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END) AS fail_count
FROM
students s
JOIN
scores sc ON s.student_id = sc.student_id
WHERE
sc.subject = '数学'
AND sc.exam_date = '2024-6-30'
GROUP BY
s.class_code
)
SELECT
class,
CONCAT(
excellent_count,
' ,',
ROUND(excellent_count * 100.0 / NULLIF(total_student, 0), 2),
'%'
) AS excellent,
CONCAT(
good_count,
' ,',
ROUND(good_count * 100.0 / NULLIF(total_student, 0), 2),
'%'
) AS good,
CONCAT(
pass_count,
' ,',
ROUND(pass_count * 100.0 / NULLIF(total_student, 0), 2),
'%'
) AS pass,
CONCAT(
fail_count,
' ,',
ROUND(fail_count * 100.0 / NULLIF(total_student, 0), 2),
'%'
) AS fail
FROM
class_stats;
withscore1 as(
select
s.class_code as class,
s.student_idas id,
case
when sc.score >= 110 then '优秀'
when sc.score >= 90then'良好'
when sc.score >= 60then'及格'
else '不及格'
end as score_def
from
students s
join
scores sc on s.student_id = sc.student_id
where
subject = '数学'
and sc.exam_date = '2024-6-30'
)
SELECT
class,
SUM(CASE WHEN score_def = '优秀' THEN 1 ELSE 0 END) AS excellent_count,
SUM(CASE WHEN score_def = '良好' THEN 1 ELSE 0 END) AS good_count,
SUM(CASE WHEN score_def = '及格' THEN 1 ELSE 0 END) AS pass_count,
SUM(CASE WHEN score_def = '不及格' THEN 1 ELSE 0 END) AS fail_count
FROM score1
GROUP BY class
order by CLASS;
withscore1 as(
select
s.class_code as class,
s.student_idas id,
case
when sc.score >= 110 then '优秀'
when sc.score >= 90then'良好'
when sc.score >= 60then'及格'
else '不及格'
end as score_def
from
students s
join
scores sc on s.student_id = sc.student_id
where
subject = '数学'
and sc.exam_date = '2024-6-30'
)
SELECT
class,
SUM(CASE WHEN score_def = '优秀' THEN 1 ELSE 0 END) AS excellent_count,
SUM(CASE WHEN score_def = '良好' THEN 1 ELSE 0 END) AS good_count,
SUM(CASE WHEN score_def = '及格' THEN 1 ELSE 0 END) AS pass_count,
SUM(CASE WHEN score_def = '不及格' THEN 1 ELSE 0 END) AS fail_count
FROM score1
GROUP BY class
order by class;
WITH TS AS (
SELECT
s.class_code,
s.name,
MAX(CASE WHEN sc.subject = '语文' THEN sc.score END) AS chinese_score,
MAX(CASE WHEN sc.subject = '数学' THEN sc.score END) AS math_score,
MAX(CASE WHEN sc.subject = '英语' THEN sc.score END) AS english_score,
MAX(CASE WHEN sc.subject = '物理' THEN sc.score END) AS physics_score,
MAX(CASE WHEN sc.subject = '化学' THEN sc.score END) AS chemistry_score,
MAX(CASE WHEN sc.subject = '生物' THEN sc.score END) AS biology_score,
MAX(CASE WHEN sc.subject = '地理' THEN sc.score END) AS geography_score,
MAX(CASE WHEN sc.subject = '历史' THEN sc.score END) AS history_score,
MAX(CASE WHEN sc.subject = '政治' THEN sc.score END) AS politics_score,
COALESCE(MAX(CASE WHEN sc.subject = '语文' THEN sc.score END), 0) +
COALESCE(MAX(CASE WHEN sc.subject = '数学' THEN sc.score END), 0) +
COALESCE(MAX(CASE WHEN sc.subject = '英语' THEN sc.score END), 0) +
COALESCE(MAX(CASE WHEN sc.subject = '物理' THEN sc.score END), 0) +
COALESCE(MAX(CASE WHEN sc.subject = '化学' THEN sc.score END), 0) +
COALESCE(MAX(CASE WHEN sc.subject = '生物' THEN sc.score END), 0) +
COALESCE(MAX(CASE WHEN sc.subject = '地理' THEN sc.score END), 0) +
COALESCE(MAX(CASE WHEN sc.subject = '历史' THEN sc.score END), 0) +
COALESCE(MAX(CASE WHEN sc.subject = '政治' THEN sc.score END), 0) AS total_score
FROM
students s
JOIN
scores sc ON s.student_id = sc.student_id
WHERE
sc.exam_date = '2024-6-30'
GROUP BY
s.class_code, s.student_id, s.name
)
SELECT *
FROM TS x
WHERE total_score >= ALL (
SELECT total_score
FROM TS y
WHERE y.class_code = x.class_code
)
ORDER BY x.class_code;