一条复杂的SQL笔试题

Question

id bill_id amt
1 1 100
1 2 200
1 3 300
1 5 400
1 7 500
2 3 100
2 4 100
3 1 100需求：找出每一员工所做的订单号连续的订单中的第一条记录，应该输出：id bill_no amt
1 1 100
1 5 400
1 7 500
2 3 100
3 1 100

Answer 1

with tt as(select 1 id,1 bill_id,100 amt from dual
  union all select 1,2,200 from dual
  union all select 1,3,300 from dual
  union all select 1,5,400 from dual
  union all select 1,7,500 from dual 
  union all select 2,3,100 from dual
  union all select 2,4,100 from dual
  union all select 3,1,100 from dual)select id,min(bill_id),min(amt)keep(dense_rank first order by bill_id)amt
from(
  select tt.*,row_number()over(partition by id order by bill_id)rn
  from tt)
group by id,bill_id-rn

Answer 2

楼上的答案不对喔。原题中amt不是从小到大来的，没有规律

Answer 3

CREATE TABLE bill as (
select 1 id ,1 bill_id, 100 amt  from dual 
union all 
select 1 ,2, 200 from dual 
union all 
select 1 ,3, 300 from dual 
union all 
select 1 ,5 ,400 from dual 
union all 
select 1 ,7 ,500 from dual 
union all 
select 2, 3 ,100 from dual 
union all 
select 2 ,4 ,100 from dual 
)
--取出后面连续的记录的第一条
select id,BILL_ID,AMT from(
SELECT B.ID,B.BILL_ID,B.AMT,lead (b.bill_id) over (partition by b.id order by b.id) nextid FROM BILL B
) c
where c.nextid-bill_id <>1
union all
select * from bill where rownum <2

Answer 4

select * from billID BILL_ID AMT
1 1 100
1 2 200
1 3 300
1 5 400
1 7 500
2 3 100
2 4 100运行上面语句后,结果
ID BILL_ID AMT
1 3 300
1 5 400
1 1 100

Answer 5

with as里面的是测试数据，你可以忽略
你先试试吧，里面有进行排序，没有规律不要紧

Answer 6

select id,BILL_ID,AMT from(
SELECT B.ID,B.BILL_ID,B.AMT,nvl(lag (b.bill_id) over (partition by b.id order by b.id),-1) nextid FROM BILL B
) c
where bill_id-c.nextid <> 1这个正解了:
ID BILL_ID AMT
1 1 100
1 5 400
1 7 500
2 3 100

Answer 7

with temp as(
select 1 id,1 bill_id,100 amt from dual 
  union all select 1,2,200 from dual 
  union all select 1,3,300 from dual 
  union all select 1,5,400 from dual 
  union all select 1,7,500 from dual 
  union all select 2,3,100 from dual 
  union all select 2,4,100 from dual 
  union all select 3,1,100 from dual
  )  
  select id,bill_id,amt from(
  select id,bill_id,amt, nvl(lag (bill_id) over (partition by id order by id),-1) lastrn from temp 
  ) where bill_id - lastrn <> 1