很难的sql面试题解  记录一个超复杂的sql 实现

QQ群有人问一个超复杂的SQL实现 花了两个小时左右才完成。记录一下

构造测试用例
构造的时候，为了验证正确性，专门构造了一个余数不为1的数据，a表的id = 3的有8条 b表有5条，按上图规则，则应该 8 除5 商 1余3 那么表 id = 3的最后一条就该是 1 + 3 行

create table a(id int);

insert into a(id)
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 3 union all
select 3 union all
select 3 union all
select 3 union all
select 3 union all
select 3 union all
select 3 union all
select 3 

create table b(id int,val char(1));
insert into b(id,val)
select 1,'A' UNION ALL
select 1,'B' UNION ALL
select 2,'C' UNION ALL
select 2,'D' UNION ALL
select 2,'E' UNION ALL

select 3,'F' UNION ALL
SELECT 3,'G' UNION ALL
SELECT 3,'H' UNION ALL
SELECT 3,'I' UNION ALL
SELECT 3,'J'

实现语句

select * from 
(
	select t2.*,if((allcnt mod cnt <> 0)  and (id= @id) and (@val = val) ,cast(allorder as signed)- cast(allcnt mod cnt as signed),allorder ) as a1, allcnt div cnt as a2, if(id <> @id,@val :=val,@val := @val),if(id <> @id,@id :=id,@id := @id) from 
	(
		SELECT a.id,t.val,t.cnt ,row_number() over(partition by t.id,t.val order by t.id,t.val) as allorder,(select count(*) from a as a1 where a1.id = a.id)  as allcnt 
        FROM A
		inner join 
		(SELECT *,(select count(*) from b as b2 where b1.id = b2.id) as cnt FROM  b as b1) t
		on a.id = t.id 
		order by a.id desc,t.val desc,allorder desc
	) t2
	,(select @id := id,@val := val from b order by id desc,val desc limit 1) as t3
) t4 where  a1 <= a2
order by id,val, allorder

最终结果