select distinct a.courtid, b.courtname, (regexp_matches(a.apiurl, '(\d+)80'))[1]::integer tn, b.state
from place a left join court b on b.courtid=a.courtid order by tn

select b.nick, a.score from(
    select teamid, sum(case offensive when 'free' then 1
    when 'far' then 3 else 2 end*cnt) as score from(
        select teamid, offensive, count(0) as cnt
        from notify where actid=93 and state=1 and jieguo='goal'
        group by teamid, offensive
    ) a group by teamid
) a left join teams b on b.teamid=a.teamid

alter table clip_tasks add column starid integer generated always as (
    case when kind=1 then refid when tempname in ('得分王集锦', '得分王集锦（速览版）') then
    substring(result->>'attach' from '\d+$')::integer else 0 end
) stored

with allbind as (
    -- 已绑定的球星
    select uid, starid from stars where uid>0
),
allclaim as (
    -- 有认领记录的 5v5 球星
    select claid, uid, (ext->>'starid')::integer starid from claim_shot where kind=0
),
unbind as (
    -- 尚未认领的 5v5 球星
    select a.* from allbind a
    left join allclaim b on b.uid=a.uid and b.starid=a.starid
    where b.claid is null
),
allnews as (
    -- 所有需要同步的数据
    select a.uid, c.courtid, c.placeid, c.actdate playday, 0 kind,
    jsonb_build_object(
        'actid', b.actid, 'starid', b.starid, 'shots', b.shots, 'team', d.nick,
        'jersey', b.jersey, 'icon', b.bodyimg, 'score', b.score
    ) ext
    from unbind a left join teamusers b on b.starid=a.starid
    left join activity c on c.actid=b.actid left join teams d on d.teamid=b.teamid
)
-- insert into claim_shot(uid, courtid, placeid, playday, kind, ext)
select * from allnews where courtid>0

with paytop as (
    select uid, count(0) paynum, round(sum(fee)/100.00, 2) payfee, max(paytime) lasttime, min(paytime) mintime
    from payment group by uid order by payfee desc limit 50
)
select a.*, b.nick, b.phone from paytop a
left join users b on b.uid=a.uid order by a.payfee desc

with courtact as (
    select a.actid, a.actdate, a.courtid, a.pv, b.teamid1, b.teamid2, b.public1+b.public2>0 haspub
    from activity a left join act5v5 b on b.actid=a.actid
    where a.state>0 and a.courtid=1 -- 此处指定场馆编号
),
topone as (
    select a.courtid, a.actdate, c.nick 主队, d.nick 客队, e.nick 此球队, f.nick 球员, a.jersey 球衣, a.score 得分
    from (
        select a.courtid, a.actid, b.starid, b.teamid, b.score, a.actdate, b.jersey
        from courtact a left join teamusers b on b.actid=a.actid
        where b.score>0 order by b.score desc limit 1
    ) a left join act5v5 b on b.actid=a.actid
    left join teams c on c.teamid=b.teamid1
    left join teams d on d.teamid=b.teamid2
    left join teams e on e.teamid=a.teamid
    left join stars f on f.starid=a.starid
),
toppv as (
    select a.courtid, a.actdate pv日期, a.pv, b.nick pv主队, c.nick pv客队
    from courtact a left join teams b on b.teamid=a.teamid1
    left join teams c on c.teamid=a.teamid2
    order by a.pv desc limit 1
),
unlocked as (
    select courtid, mon 解锁月, num 解锁次 from (
        select courtid, mon, count(0) num from (
            select courtid, date_trunc('month', actdate)::char(7) mon from courtact where haspub
        ) a group by courtid, mon order by num desc limit 1
    ) a
)
select d.courtname, a., b.*, c. from topone a left join toppv b on b.courtid=a.courtid
left join unlocked c on c.courtid=a.courtid
left join court d on d.courtid=a.courtid

with dates as (select '2024-01-01'::date sdate, '2024-05-01'::date edate),
allteam as (
    -- 所有
    select a.teamid, array_agg(b.courtname) courts, sum(actnum) actnum, max(lastid) lastid
    from (
        select a.teamid, c.courtid, count(c.actid) actnum, max(c.actid) lastid
        from teams a left join act5v5 b on a.teamid in (b.teamid1, b.teamid2)
        left join activity c on c.actid=b.actid
        where c.state>0 group by a.teamid, c.courtid
    ) a left join court b on b.courtid=a.courtid group by a.teamid
),
pays as (
    -- 指定时间段内的支付记录
    select payid, api, fee, paytime, refid from dates a
    left join payment b on b.paytime between a.sdate and a.edate
    and b.api='UnlockTeam'
),
byteam as (
    -- 球队各版本解锁费用
    select b.teamid, a.fee, case b.skuid when 3 then 2.0 else 1.0 end ver
    from pays a left join act5v5_team_order b on b.ordid=a.refid
),
sumfee as (
    select teamid, ver, sum(fee/100) total from byteam group by teamid, ver
),
summem as (
    select a.teamid, count(b.uid) memnum
    from allteam a left join teammembs b on b.teamid=a.teamid and b.state=1
    group by a.teamid
)
select e.nick, a.teamid, a.actnum, c.total total1, d.total total2,
coalesce(e.ext->>'hot', '0')::integer+e.pv hot, g.memnum, f.courtname, b.actdate, a.courts
from allteam a left join activity b on b.actid=a.lastid
left join sumfee c on c.teamid=a.teamid and c.ver=1.0
left join sumfee d on d.teamid=a.teamid and d.ver=2.0
left join teams e on e.teamid=a.teamid
left join court f on f.courtid=b.courtid
left join summem g on g.teamid=a.teamid
order by a.actnum desc