2001
2004
2002
2003
2001
select tname, number, results from `2001` a left join
(select card_id,count(1) as number,
(case when max(scores)>=60 then '通过' else '未通过' end ) as results from `2001` group by card_id ) b
on a.card_id=b.card_id where number>1
select distinct a.card_id,a.tname
from `2001` a ,`2001` b where a.open_id =b.open_id and a.card_id <> b.card_id
select distinct (first_value(a.tname) over(PARTITION by a.open_id order by a.inserttime asc) )tname,
(max(a.scores) over(PARTITION by a.open_id) )max_scor
from `2001` a ,`2001` b where a.open_id =b.open_id and a.card_id <> b.card_id
select count(1),max(scores) from
(select tname, max(scores) scores from `2001` where tname
not in (select DISTINCT a.tname
from `2001` a ,`2001` b where COALESCE(length(a.card_id),0)<>18 or
(a.open_id =b.open_id and a.card_id <> b.card_id ) ) and scores>=60 group by tname ) c
2002
select seat,seat2 from
(select * ,(lead(seat,1) over(order by seat)) seat2,(lead(status,1) over(order by seat))status2 from `2002` ) a where status='未预订' and status2 = '未预订'
select concat(seat,'、',seat2) seatgroup from
(select * ,(lead(seat,1) over(order by seat)) seat2,(lead(status,1) over(order by seat))status2 from `2002` ) a where status='未预订' and status2 = '未预订'
2003
SELECT CONCAT_WS('~', s1.seat, s4.seat) AS seat_group
FROM `2003` s1
JOIN `2003` s2 ON s2.rowid = s1.rowid AND s2.seat = s1.seat + 1 AND s2.status = '未预订'
JOIN `2003` s3 ON s3.rowid = s2.rowid AND s3.seat = s2.seat + 1 AND s3.status = '未预订'
JOIN `2003` s4 ON s4.rowid = s3.rowid AND s4.seat = s3.seat + 1 AND s4.status = '未预订'
where s1.status = '未预订'
2004
select orderid ,sum(amount) sum_price from
(select orderid,(cast(sale_amount as decimal) * cast(price as decimal(32,2))) amount from 2004a a left join 2004b b on a.PRODUCTID=b.PRODUCTID ) c group by orderid
select ROW_NUMBER() OVER (ORDER BY amount desc) as id, ztime, amount from
(select orderid ,ztime,customid,(cast(sale_amount as decimal) * cast(price as decimal(32,2))) amount from 2004a a left join 2004b b on a.PRODUCTID=b.PRODUCTID where customid = 'C004') c order by amount desc limit 3