一个如何编写SQL语句问题。

Question

有如下表，同一种key，可能对应不同的level。

需求是：传入一个id list，要求返回的数据为：
按照key分组，只统计id里对应的最高的那个level的数量，例如，传入的idlist是[2,3]，那么返回的数据应该为a,3,1（3代表最高level，1代表数量），传入的idlist是[2,3,8,9]，返回的数据是a,3,1；c,2,2。
有没有一条sql能够解决的方案呢！（sql支持find in操作，例如find in (id1,id2...)）

Answer 1

表：

CREATE TABLE `t_keys` (
  `id` int(11) DEFAULT NULL,
  `k` varchar(20) DEFAULT NULL,
  `level` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

sql:下面两种都可以

//第一种写法
select a.k,a.level ,count(1) as "count" from t_keys a where a.id in(2,3)
and a.level >=  (select max(b.level) from  t_keys b where b.k = a.k and b.id in(2,3))
group by a.k,a.level;


//第二种写法
select a.k,max(a.level) as "level",sum(
    case 
            when a.level >=  (select max(b.level) from  t_keys b where b.k = a.k and b. id in(2,3) ) then 1 else 0
   end
) as "count" from t_keys a where a.id in(2,3) group by a.k;

Answer 2

先写个复杂点的

select t1.`key`,t1.max_lev,count(1)
from (
select `key`,max(`level`) max_lev
 from seg_key_level 
 where id in (2,3,8,9)
 group by `key`
)t1 inner join seg_key_level t2 on t1.`key`=t2.`key` and t1.max_lev=t2.`level`
where t2.id in (2,3,8,9)
group by t1.`key`,t1.max_lev;