select a.* from demo a where 2 > 
(
 select count(*) from demo where name = a.name and val > a.val 
) 

--1
select a.*,(select count(*) from demo where name = a.name and val > a.val )
from demo a where 2 > 
(
 select count(*) from demo where name = a.name and val > a.val 
) 

--2
select * from (
select a.*,(select count(*) from demo where name = a.name and val > a.val ) as cnt
) t where cnt >2

------解决方案--------------------

就是说 a 表中的每一行 与 demo 表比较 ：
  name = a.name and val > a.val 这个是关联条件
这个
select count(*) from demo where name = a.name and val > a.val
在name相同的情况下，返回当整张表VAL比当前行VAL大的个数