使用两表联合，条件是商品id相等和用户ID相等，但是在where语句里过滤收藏时间早于购买时间却产生了奇怪的结果：收藏用户和购买用户数量相等了。代码如下：
with purchase_cnt as (
select
f.mch_id,
count(distinct f.cust_uid) as fav_user_cnt,
count(distinct p.cust_uid) as pcs_user_cnt
from
xhs_fav_rcd f
left join
xhs_pchs_rcd p on p.mch_id = f.mch_id and p.cust_uid = f.cust_uid
where
p.pchs_tm > f.fav_tm
group by
f.mch_id
)
select
mch_id,
fav_user_cnt,
pcs_user_cnt,
round(100 * cast(pcs_user_cnt as float) / fav_user_cnt, 2) as convert_rate
from
purchase_cnt
group by
mch_id,
fav_user_cnt,
pcs_user_cnt
order by
convert_rate desc;
后来把 p.pchs_tm > f.fav_tm 写在了on里面 on p.mch_id = f.mch_id and p.cust_uid = f.cust_uid and p.pchs_tm > f.fav_tm，结果就都对了。为什么会这样？
起点是餐饮、终点是餐饮两个子查询，同时在两个集合中的地点：id相同，且时间相同
with start_catering as (
select
r.cust_uid,
r.start_loc,
r.end_loc,
r.start_tm,
r.car_cls
from
didi_sht_rcd r
inner join
loc_nm_ctg l on l.loc_nm = r.start_loc
where
l.loc_ctg = '餐饮'
),
end_catering as (
select
r.cust_uid,
r.start_loc,
r.end_loc,
r.start_tm,
r.car_cls
from
didi_sht_rcd r
inner join
loc_nm_ctg l on l.loc_nm = r.end_loc
where
l.loc_ctg = '餐饮'
)
select
s.*
from
start_catering s
inner join
end_catering e on s.cust_uid = e.cust_uid and s.start_tm = e.start_tm
order by
s.start_tm asc
;
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
;
这样写有问题吗？未及格学生人数和正确结果有1-3的误差，不知为什么。
with score_ranges as (
select
st.class_code,
count(*) as class_total,
sum(case when sc.score >= 110 then 1 else 0 end) as excellent,
sum(case when sc.score between 90 and 109 then 1 else 0 end) as good,
sum(case when sc.score between 60 and 89 then 1 else 0 end) as pass,
sum(case when sc.score < 60 then 1 else 0 end) as fail
from
students st
inner join
scores sc on sc.student_id = st.student_id
where
exam_date = '2024-06-30' and sc.subject = '数学'
group by
st.class_code
)
select
class_code,
class_total,
concat(excellent, ' ,', round(100 * excellent / class_total, 2), '%') as excellent,
concat(good, ' ,', round(100 * good / class_total, 2), '%') as good,
concat(pass, ' ,', round(100 * pass / class_total, 2), '%') as pass,
concat(fail, ' ,', round(100 * fail / class_total, 2), '%') as fail
from score_ranges
group by class_code
order by class_code;
with total_levels as (
select
student_id,
(case when score >= 110 then 1 else 0 end) as excellent,
(case when score between 90 and 109 then 1 else 0 end) as good,
(case when score between 60 and 89 then 1 else 0 end) as pass,
(case when score < 60 then 1 else 0 end) as fail
from scores
where exam_date = '2024-06-30' and subject = '数学'
)
select
s.class_code,
sum(excellent) as excellent,
sum(good) as good,
sum(pass) as pass,
sum(fail) as fail
from
total_levels as t
inner join
students as s on s.student_id = t.student_id
group by
s.class_code
order by
s.class_code;
a=0,b>0时，c是否应该>0才能经过第二象限？
a>0时，是否不需要其他条件曲线都能经过第二象限？
select *
from numbers_for_fun
where
(
a = 0 and
(
(b > 0 and c > 0)
or
(b < 0)
or
(b = 0 and c < 0)
)
)
or a > 0
or (
a < 0 and
(
(b > 0 and c > 0)
or
(b < 0 and c > b*b/4/a)
)
);
这题考的就是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。
sum(
case
when chinese >= 110 then 1
when math >= 110 then 1
when english >= 110 then 1
else 0
end
) >= 2
这种判断不能按预想的执行，因为case when 短路了，第一个条件执行完后面的就不判断了，所以sum永远不能得到2
with trx_amt_counting as (
select
substr(r.trx_time, 1, 7) as trx_mon,
sum(r.trx_amt) as trx_amt
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm
where
r.trx_time between '2023-01-01' and '2024-07-01'
and
r.usr_id = 5201314520
and
r.trx_amt >= 288
and
(t.mch_typ = '休闲娱乐' or t.mch_nm is null)
and
hour(r.trx_time) in (23, 0, 1, 2)
group by
trx_mon
)
select
substr(max(d.date_value), 1, 7) as trx_mon,
case when t.trx_amt is null then '1900-01-01' else last_day(max(d.date_value)) end as last_day,
case when t.trx_amt is null then 0 else dayofmonth(last_day(max(d.date_value))) end as day_of_mon,
coalesce(t.trx_amt, '0') as trx_amt,
count(*) as trx_cnt,
coalesce(round(t.trx_amt / dayofmonth(last_day(max(d.date_value))), 2), 0) as avg_day_amt,
coalesce(round(count(*) / dayofmonth(last_day(max(d.date_value))), 2), 0) as avg_day_cnt
from
date_table d
left join
trx_amt_counting t on t.trx_mon = substr(d.date_value, 1, 7)
group by
trx_mon,
t.trx_amt
order by
trx_mon;
select
date_format(r.trx_time, '%Y-%m') as trx_mon,
sum(r.trx_amt) as trx_amt
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on r.mch_nm = t.mch_nm
where
usr_id = 5201314520
and
date_format(r.trx_time, '%Y-%m') between '2023-01' and '2024-12'
and
t.mch_typ = '休闲娱乐'
group by
trx_mon
order by
trx_mon;
select
substr(r.trx_time, 1, 7) as trx_mon,
case when sum(r.trx_amt) = 0 then '1900-01-01' else last_day(max(r.trx_time)) end as last_day,
dayofmonth(last_day(max(r.trx_time))) as day_of_month,
sum(r.trx_amt) as trx_amt,
count(*) as trx_cnt,
round(sum(r.trx_amt) / dayofmonth(last_day(max(r.trx_time))), 2) as avg_day_amt,
round(count(*) / dayofmonth(last_day(max(r.trx_time)))) as avg_day_cnt
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm
where
(t.mch_typ = '休闲娱乐'
or
t.mch_typ is null)
and
r.trx_amt > 288
and
hour(r.trx_time) in (23, 0, 1, 2)
and
trx_time between '2023-01-01' and '2024-07-01'
group by
trx_mon
order by
trx_mon asc
;
select
substr(r.trx_time, 1, 7) as trx_mon,
last_day(max(r.trx_time)) as last_day,
day(last_day(max(r.trx_time))) as days_of_mon,
sum(r.trx_amt) as trx_amt,
count(*) as trx_cnt,
sum(r.trx_amt) / day(last_day(max(r.trx_time))) as avg_day_amt,
count(r.trx_time) / dayofmonth(last_day(max(r.trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm
where
r.usr_id = '5201314520'
and
year(r.trx_time) in (2023, 2024)
and
t.mch_typ = '休闲娱乐'
group by
trx_mon
order by
trx_mon asc;
select
substr(r.trx_time, 1, 7) as trx_mon,
last_day(max(r.trx_time)) as last_day,
day(last_day(max(r.trx_time))) as days_of_mon,
sum(r.trx_amt) as trx_amt,
count(*) as trx_cnt,
sum(r.trx_amt) / count(*) as avg_day_amt,
count(r.trx_time) / dayofmonth(last_day(max(r.trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm
where
r.usr_id = '5201314520'
and
year(r.trx_time) in (2023, 2024)
and
t.mch_typ = '休闲娱乐'
group by
trx_mon
order by
trx_mon asc;
select
substr(r.trx_time, 1, 7) as trx_mon,
last_day(max(r.trx_time)) as last_day,
day(last_day(max(r.trx_time))) as days_of_mon,
sum(r.trx_amt) as trx_amt,
count(*) as trx_cnt,
sum(r.trx_amt) / count(r.trx_time) as avg_day_amt,
count(r.trx_time) / dayofmonth(last_day(max(r.trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm
where
r.usr_id = '5201314520'
and
year(r.trx_time) in (2023, 2024)
and
t.mch_typ = '休闲娱乐'
group by
trx_mon
order by
trx_mon asc;
select
substr(r.trx_time, 1, 7) as trx_mon,
last_day(max(r.trx_time)) as last_day,
day(last_day(max(r.trx_time))) as days_of_mon,
sum(r.trx_amt) as trx_amt,
count(r.trx_time) as trx_cnt,
sum(r.trx_amt) / count(r.trx_time) as avg_day_amt,
count(r.trx_time) / dayofmonth(last_day(max(r.trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm
where
r.usr_id = '5201314520'
and
year(r.trx_time) in (2023, 2024)
and
t.mch_typ = '休闲娱乐'
group by
trx_mon
order by
trx_mon asc;
select
substr(r.trx_time, 1, 7) as trx_mon,
last_day(max(r.trx_time)) as last_day,
dayofmonth(last_day(max(r.trx_time))) as days_of_mon,
sum(r.trx_amt) as trx_amt,
count(r.trx_time) as trx_cnt,
sum(r.trx_amt) / count(r.trx_time) as avg_day_amt,
count(r.trx_time) / dayofmonth(last_day(max(r.trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm
where
r.usr_id = '5201314520'
and
year(r.trx_time) in (2023, 2024)
and
t.mch_typ = '休闲娱乐'
group by
trx_mon
order by
trx_mon asc;
select
substr(trx_time, 1, 7) as trx_mon,
last_day(max(trx_time)) as last_day,
dayofmonth(last_day(max(trx_time))) as days_of_mon,
sum(trx_amt) as trx_amt,
count(trx_time) as trx_cnt,
sum(trx_amt) / count(trx_time) as avg_day_amt,
count(trx_time) / dayofmonth(last_day(max(trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
year(trx_time) in (2023, 2024)
group by
trx_mon
order by
trx_mon asc;
select
left(r.trx_time, 7) as trx_mon,
last_day(max(r.trx_time)) as last_day,
dayofmonth(last_day(max(r.trx_time))) as day_of_mon
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm
where
r.usr_id = '5201314520'
and
year(r.trx_time) in (2023, 2024)
and
t.mch_typ = '休闲娱乐'
group by
trx_mon
order by
trx_mon asc
;
select
left(r.trx_time, 7) as trx_mon,
last_day(max(r.trx_time)) as last_day,
day(last_day(max(r.trx_time))) as day_of_mon
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm
where
r.usr_id = '5201314520'
and
year(r.trx_time) in (2023, 2024)
and
t.mch_typ = '休闲娱乐'
group by
trx_mon
order by
trx_mon asc
;
select
substr(r.trx_time, 1, 7) as trx_mon,
last_day(max(r.trx_time)) as last_day,
day(last_day(max(r.trx_time))) as day_of_mon
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm
where
r.usr_id = '5201314520'
and
year(r.trx_time) in (2023, 2024)
and
t.mch_typ = '休闲娱乐'
group by
trx_mon
order by
trx_mon asc
;
select substr(trx_time,1,7) as trx_mon ,last_day(max(trx_time)) as last_day, day(last_day(max(trx_time)) ) as day_of_mon
from cmb_usr_trx_rcd a
left join cmb_mch_typ m
on a.mch_nm = m.mch_nm
where a.usr_id=5201314520 and year(a.trx_time) in (2023, 2024) and m.mch_typ='休闲娱乐'
group by substr(a.trx_time,1,7)
order by 1 ;
select
left(r.trx_time, 7) as trx_mon,
last_day(max(r.trx_time)) as last_day,
day(last_day(max(r.trx_time))) as day_of_mon
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm and t.mch_typ = '休闲娱乐'
where
r.usr_id = '5201314520'
and
year(r.trx_time) in (2023, 2024)
group by
trx_mon
order by
trx_mon asc
;
select
left(r.trx_time, 7) as trx_mon,
last_day(max(r.trx_time)) as last_day,
dayofmonth(last_day(max(r.trx_time))) as day_of_mon
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm and t.mch_typ = '休闲娱乐'
where
r.usr_id = '5201314520'
and
year(r.trx_time) in (2023, 2024)
group by
trx_mon
order by
trx_mon asc
;
select
left(trx_time, 7) as trx_mon,
last_day(trx_time) as last_day,
dayofmonth(last_day(trx_time)) as day_of_mon
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm and t.mch_typ = '休闲娱乐'
where
usr_id = '5201314520'
and
year(trx_time) in (2023, 2024)
order by
trx_mon asc
;
select
left(trx_time, 7) as trx_mon,
last_day(trx_time) as last_day,
dayofmonth(trx_time) as day_of_mon
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm and t.mch_typ = '休闲娱乐'
where
usr_id = '5201314520'
and
year(trx_time) in (2023, 2024)
order by
trx_mon asc
;
select
t.mch_typ,
r.mch_nm,
count(r.mch_nm) as trx_cnt,
sum(r.trx_amt) as trx_amt
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on r.mch_nm = t.mch_nm
where
r.usr_id = '5201314520'
and
left(r.trx_time, 4) = '2024'
group by
t.mch_typ,
r.mch_nm
having
t.mch_typ is null
order by
trx_cnt desc;
select
t.mch_typ,
count(r.mch_nm) as trx_cnt,
sum(r.trx_amt) as trx_amt
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm
where
r.usr_id = '5201314520'
and
year(r.trx_time) = 2024
group by
t.mch_typ
order by
trx_cnt desc;
select
t.mch_typ,
count(distinct r.mch_nm) as trx_cnt,
sum(r.trx_amt) as trx_amt
from
cmb_usr_trx_rcd r
left join
cmb_mch_typ t on t.mch_nm = r.mch_nm
where
r.usr_id = '5201314520'
group by
t.mch_typ
order by
trx_cnt desc;
使用两表联合，条件是商品id相等和用户ID相等，但是在where语句里过滤收藏时间早于购买时间却产生了奇怪的结果：收藏用户和购买用户数量相等了。代码如下： with purchase_cnt as ( select f.mch_id, count(distinct f.cust_uid) as fav_user_cnt, count(distinct p.cust_uid) as pcs_user_cnt from xhs_fav_rcd f left join xhs_pchs_rcd p on p.mch_id = f.mch_id and p.cust_uid = f.cust_uid where p.pchs_tm > f.fav_tm group by f.mch_id ) select mch_id, fav_user_cnt, pcs_user_cnt, round(100 * cast(pcs_user_cnt as float) / fav_user_cnt, 2) as convert_rate from purchase_cnt group by mch_id, fav_user_cnt, pcs_user_cnt order by convert_rate desc; 后来把 p.pchs_tm > f.fav_tm 写在了on里面 on p.mch_id = f.mch_id and p.cust_uid = f.cust_uid and p.pchs_tm > f.fav_tm，结果就都对了。为什么会这样？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 ; 这样写有问题吗？未及格学生人数和正确结果有1-3的误差，不知为什么。select name, subject, class_code from teachers where class_code like 'C___C___C___' order by name asc;select name, subject, class_code from teachers where class_code like 'C___C___C___C___C___' order by name asc;with score_ranges as ( select st.class_code, count(*) as class_total, sum(case when sc.score >= 110 then 1 else 0 end) as excellent, sum(case when sc.score between 90 and 109 then 1 else 0 end) as good, sum(case when sc.score between 60 and 89 then 1 else 0 end) as pass, sum(case when sc.score < 60 then 1 else 0 end) as fail from students st inner join scores sc on sc.student_id = st.student_id where exam_date = '2024-06-30' and sc.subject = '数学' group by st.class_code ) select class_code, class_total, concat(excellent, ' ,', round(100 * excellent / class_total, 2), '%') as excellent, concat(good, ' ,', round(100 * good / class_total, 2), '%') as good, concat(pass, ' ,', round(100 * pass / class_total, 2), '%') as pass, concat(fail, ' ,', round(100 * fail / class_total, 2), '%') as fail from score_ranges group by class_code order by class_code;with total_levels as ( select student_id, (case when score >= 110 then 1 else 0 end) as excellent, (case when score between 90 and 109 then 1 else 0 end) as good, (case when score between 60 and 89 then 1 else 0 end) as pass, (case when score < 60 then 1 else 0 end) as fail from scores where exam_date = '2024-06-30' and subject = '数学' ) select s.class_code, sum(excellent) as excellent, sum(good) as good, sum(pass) as pass, sum(fail) as fail from total_levels as t inner join students as s on s.student_id = t.student_id group by s.class_code order by s.class_code;a=0,b>0时，c是否应该>0才能经过第二象限？ a>0时，是否不需要其他条件曲线都能经过第二象限？ select * from numbers_for_fun where ( a = 0 and ( (b > 0 and c > 0) or (b < 0) or (b = 0 and c < 0) ) ) or a > 0 or ( a < 0 and ( (b > 0 and c > 0) or (b < 0 and c > b*b/4/a) ) );select distinct cust_uid, mch_nm from mt_trx_rcd1 where cust_uid = 'MT10000' order by mch_nm; 也可以select * from numbers_for_fun where a = 0 and b <> 0 -(c/b) > 0 order by id; 直线与x轴的交点：bx+c=0, 那么x=-(c/b)。应该是这样的吧？sum( case when chinese >= 110 then 1 when math >= 110 then 1 when english >= 110 then 1 else 0 end ) >= 2 这种判断不能按预想的执行，因为case when 短路了，第一个条件执行完后面的就不判断了，所以sum永远不能得到2