select
t.*
from
tx_red_pkt_rcd t
join
(
select
snd_usr_id,
sum(case when pkt_amt = '520' then 1 else 0 end) as wel,
sum(case when pkt_amt = '200' then 1 else 0 end) as ell
from tx_red_pkt_rcd
group by snd_usr_id
) a
on
t.snd_usr_id = a.snd_usr_id
where
wel >= 5 or ell >= 5
order by
t.snd_usr_id,
t.snd_datetime;
想不通,为什么我写的这个不对,求教
select
s.student_id,name,
p.phy as score,
row_number()over(partition by grade_code order by p.phy desc) as rnk
from
students s
join
(
select
student_id,
max(if(subject = '物理',score,0)) as phy
from
scores
group by
student_id
) p
on
s.student_id = p.student_id
where
grade_code = 'S1'
limit
10;
我写的这个为什么不对呀?不明白
with tmp as(select city,con
from weather_rcd_china
where month(dt) in (12,1,2) and con like '%雪%')
select city,count(*) as snowy_days
from tmp
group by city
order by 2 desc;
select zn.mch_nm as asshole_tried,trx_cnt,bf.mch_nm as darling_tried
from
(select mch_nm,count(trx_time) as trx_cnt
from cmb_usr_trx_rcd
where usr_id = 5201314520 and year(trx_time) in (2023,2024)
group by mch_nm) zn
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id = 5211314521 and year(trx_time) in (2023,2024)) bf
onzn.mch_nm = bf.mch_nm
where trx_cnt >= 20
order by 2 desc;
select zn.mch_nm
from (select distinct mch_nm,usr_id
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time)=2024) zn
join
(select distinct mch_nm,usr_id
from cmb_usr_trx_rcd
where usr_id=5211314521 and year(trx_time)=2024) bf
on zn.mch_nm = bf.mch_nm
order by 1 desc;
select zn.mch_nm
from (select distinct mch_nm,usr_id
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time)=2024) zn
join
(select distinct mch_nm,usr_id
from cmb_usr_trx_rcd
where usr_id=5211314521 and year(trx_time)=2024) bf
on zn.mch_nm = bf.mch_nm
select
distinct c.mch_nm
from
cmb_usr_trx_rcd c
join
(
select
mch_nm,usr_id
from
cmb_usr_trx_rcd
where
usr_id = 5201314520
and year(trx_time)=2024
) a
on
c.mch_nm = a.mch_nm
where
c.usr_id = 5211314521
and year(trx_time)=2024
select mch_nm from
(select
mch_nm,
max(case when usr_id = 5201314520 then 1 else 0 end) as zn,
max(case when usr_id = 5211314521 then 1 else 0 end) as bf
from
cmb_usr_trx_rcd
where
year(trx_time) = 2024
group by
mch_nm
having
max(case when usr_id = 5201314520 then 1 else 0 end) = 1
and max(case when usr_id = 5211314521 then 1 else 0 end) = 1) a
select mch_nm from
(select
mch_nm,
max(case when usr_id = 5201314520 then 1 else 0 end) as zn,
max(case when usr_id = 5211314521 then 1 else 0 end) as bf
from
cmb_usr_trx_rcd
group by
mch_nm
having
max(case when usr_id = 5201314520 then 1 else 0 end) = 1
and max(case when usr_id = 5211314521 then 1 else 0 end) = 1) a
select
mch_nm
from
cmb_usr_trx_rcd
group by
mch_nm
having
max(case when usr_id = 5201314520 then 1 else 0 end) = 1
and max(case when usr_id = 5211314521 then 1 else 0 end) = 1
select
t.*
from
tx_red_pkt_rcd t
join
(
select
snd_usr_id,
sum(case when pkt_amt = '520' then 1 else 0 end) as wel,
sum(case when pkt_amt = '200' then 1 else 0 end) as ell
from tx_red_pkt_rcd
group by snd_usr_id
) a
on
t.snd_usr_id = a.snd_usr_id
where
wel >= 5 or ell >= 5
order by
t.snd_usr_id,
t.snd_datetime;
select
t.*
from
tx_red_pkt_rcd t
join
(
select
snd_usr_id,
sum(case when pkt_amt = '520' then 1 else 0 end) as wel,
sum(case when pkt_amt = '200' then 1 else 0 end) as ell
from tx_red_pkt_rcd
group by snd_usr_id
HAVING
COUNT(CASE WHEN pkt_amt IN (520,200) THEN 1 END) >=5
) a
on
t.snd_usr_id = a.snd_usr_id
order by
t.snd_usr_id,
t.snd_datetime;
select
t.*
from
tx_red_pkt_rcd t
join
(
select
snd_usr_id,
sum(case when pkt_amt = '520' then 1 else 0 end) as wel,
sum(case when pkt_amt = '200' then 1 else 0 end) as ell
from tx_red_pkt_rcd
group by snd_usr_id) a
on
a.snd_usr_id = t.snd_usr_id
where
wel >= 5 or ell >= 5
order by
t.snd_usr_id,
t.snd_datetime;
select
t.snd_usr_id,
t.rcv_usr_id,
pkt_amt,snd_datetime,
rcv_datetime
from
tx_red_pkt_rcd t
join
(
select
snd_usr_id,
sum(case when pkt_amt = '520' then 1 else 0 end) as wel,
sum(case when pkt_amt = '200' then 1 else 0 end) as ell
from tx_red_pkt_rcd
group by snd_usr_id) a
on
a.snd_usr_id = t.snd_usr_id
where
wel >= 5 or ell >= 5
order by
t.snd_usr_id,
t.snd_datetime;
select t.snd_usr_id,t.rcv_usr_id,pkt_amt,snd_datetime,rcv_datetime
from
tx_red_pkt_rcd t
join
(select
snd_usr_id,
sum(case when pkt_amt = '520' then 1 else 0 end) as wel,
sum(case when pkt_amt = '200' then 1 else 0 end) as ell
from tx_red_pkt_rcd
group by snd_usr_id) a
on a.snd_usr_id = t.snd_usr_id
where wel >= 5 or ell >= 5
order by t.snd_usr_id,snd_datetime
select
s.student_id,name,
p.phy as score,
row_number()over(partition by grade_code order by p.phy desc) as rnk
from
students s
join
(
select
student_id,
max(if(subject = '物理',score,0)) as phy
from
scores
group by
student_id
) p
on
s.student_id = p.student_id
where
grade_code = 'S1'
limit
10;