想用非，没毛病啊，select * from cmb_usr_trx_rcd
where usr_id = 5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and hour(trx_time) not between 6 and 21 ，临界点改对了就行。
这题考的就是inner join和left join的使用场景。你运行这段代码试试，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 ON s.singer_id = a.singer_id
inner JOIN
song_info sg ON a.album_id = sg.album_id
inner JOIN
listen_rcd l ON sg.song_id = l.song_id
GROUP BY
s.singer_id, s.singer_name, a.album_id, a.album_name。
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
date(login_time)
,count(*) as cnt
from
user_login_log
where
date(login_time) between '2024-09-01' and '2024-09-30'
and (usr_id,login_time)in
(select
usr_id
,min(login_time)
from
user_login_log
group by
1)
group by
1
order by
1
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
COUNT(DISTINCT CASE
WHEN TIME(login_time) BETWEEN '07:30:00' AND '09:30:00'
OR TIME(login_time) BETWEEN '18:30:00' AND '20:30:00' THEN usr_id
END) AS commute,
COUNT(DISTINCT CASE
WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' THEN usr_id
END) AS lunch_break,
COUNT(DISTINCT CASE
WHEN TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' THEN usr_id
WHEN TIME(login_time) BETWEEN '00:00:00' AND '01:00:00' THEN usr_id
END) AS bedtime
FROM
user_login_log
WHERE
login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01');
SELECT
distinct snd_usr_id
FROM
tx_red_pkt_rcd r
WHERE
r.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 (520,200) THEN 1 END) >=5
)
select * from cmb_usr_trx_rcd
where usr_id = 5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and hour(trx_time) not between 6 and 21
select * from cmb_usr_trx_rcd
where usr_id = 5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and hour(trx_time) not between 6 and 24
order by trx_time
select * from cmb_usr_trx_rcd
where usr_id = 5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and hour(trx_time) not between 6 and 23
order by trx_time
select * from cmb_usr_trx_rcd
where usr_id = 5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and hour(trx_time) not between 6 and 22
order by trx_time
select
*
from
cmb_usr_trx_rcd
where
date(trx_time)
between '2024-09-01' and '2024-09-30'
and (
(hour(trx_time) >= 22)
or
(hour(trx_time) between 0 and 5)
)
and usr_id = '5201314520'
order by trx_time
select * from cmb_usr_trx_rcd
where usr_id = 5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and hour(trx_time) not between 5 and 23
order by trx_time
select * from cmb_usr_trx_rcd
where usr_id = 5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and hour(trx_time) not between 5 and 22
order by trx_time
select
s.student_id,
s.name,
sc.score,
row_number()over(order by sc.score desc) as rnk
from students s
left join scores sc
on sc.student_id = s.student_id
where sc.subject = '物理'
and s.grade_code = 'S1'
order by rnk,s.student_id
limit 10;
select
t.name,
count(distinct s.student_id) as total_stu,
count(distinct (case when sc.score < 60 then s.student_id else null end)) as unpass_stu,
count(distinct case when sc.score < 60 then s.student_id else null end) / count(distinct s.student_id) as up_rate
from students s
inner join
teachers t on t.class_code like concat('%', s.class_code, '%')
inner join
scores sc on s.student_id = sc.student_id
where
t.subject = '化学' and sc.subject='化学'
group by
t.name
;
WITH total_students AS (
SELECT
t.name AS teacher_name,
COUNT(DISTINCT s.student_id) AS total_students
FROM
teachers t
JOIN
students s ON t.class_code LIKE CONCAT('%', s.class_code, '%')
WHERE
t.subject = '化学'
GROUP BY
t.name
),
failed_students AS (
SELECT
t.name AS teacher_name,
COUNT(DISTINCT s.student_id) AS failed_students
FROM
teachers t
JOIN
students s ON t.class_code LIKE CONCAT('%', s.class_code, '%')
JOIN
scores sc ON s.student_id = sc.student_id
WHERE
t.subject = '化学'
AND sc.subject = '化学'
AND sc.score < 60
GROUP BY
t.name
)
SELECT
ts.teacher_name,
ts.total_students,
fs.failed_students,
ROUND((fs.failed_students / ts.total_students) * 100, 2) AS failure_rate
FROM
total_students ts
JOIN
failed_students fs ON ts.teacher_name = fs.teacher_name
ORDER BY
failure_rate DESC;
select
t.name,
count(distinct s.student_id) as total_stu,
count(distinct (case when sc.score < 60 then s.student_id else null end)) as unpass_stu,
count(distinct case when sc.score < 60 then s.student_id else null end) / count(distinct s.student_id) as up_rate
from students s
inner join
teachers t on t.class_code like concat('%', s.class_code, '%')
inner join
scores sc on s.student_id = sc.student_id
where
t.subject = '化学'
group by
t.name
;
WITH ranked_red_packets AS (
SELECT
r.pkt_amt,
ROW_NUMBER() OVER (ORDER BY r.pkt_amt) AS row_num,
COUNT(*) OVER () AS total_count
FROM
tx_red_pkt_rcd r
WHERE
DATE(r.snd_datetime) = '2021-02-13'
)
SELECT
AVG(pkt_amt) AS median
FROM
ranked_red_packets
WHERE
row_num IN ((total_count + 1) / 2, (total_count + 2) / 2);
同学，两个点说明了你的基础很薄弱哈。 1.请问date('2024-09-30 12:23:12')输出的日期是什么？你写成了betwwen 的尾巴'2024-10-01' ，那会把这天的数据也包括进去的； 2.and 和or区分好，只要学过初中英语你就能会啊！