SQL面试题

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