建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。 select * from ( select p.类型,p.ID,p.名称,count(*) as cnt from 订单 o inner join 订单详细 i on o.订单号=i.订单号 and o.成功=1 inner join 产品 p on i.产品ID=p.ID group by p.类型,p.ID,p.名称 ) t where 20>(select count(*) from ( select p.类型,p.ID,p.名称,count(*) as cnt from 订单 o inner join 订单详细 i on o.订单号=i.订单号 and o.成功=1 inner join 产品 p on i.产品ID=p.ID group by p.类型,p.ID,p.名称 ) x where x.cnt>t.cnt and x.类型=t.类型 ) order by 类型,cnt desc
select * from ( select (select count(*) from (select p.类型,p.ID,p.名称,count(*) as cnt from 订单 o inner join 订单详细 i on o.订单号=i.订单号 and o.成功=1 inner join 产品 p on i.产品ID=p.ID group by p.类型,p.ID,p.名称 ) b where b.cnt>=a.cnt and b.类型=a.类型 ) as GroupID, a.* from (select p.类型,p.ID,p.名称,count(*) as cnt from 订单 o inner join 订单详细 i on o.订单号=i.订单号 and o.成功=1 inner join 产品 p on i.产品ID=p.ID group by p.类型,p.ID,p.名称 ) a ) c where c.GroupID<=20 order by c.类型,c.GroupID
select *
from (
select p.类型,p.ID,p.名称,count(*) as cnt
from 订单 o inner join 订单详细 i on o.订单号=i.订单号 and o.成功=1
inner join 产品 p on i.产品ID=p.ID
group by p.类型,p.ID,p.名称
) t
where 20>(select count(*) from (
select p.类型,p.ID,p.名称,count(*) as cnt
from 订单 o inner join 订单详细 i on o.订单号=i.订单号 and o.成功=1
inner join 产品 p on i.产品ID=p.ID
group by p.类型,p.ID,p.名称
) x
where x.cnt>t.cnt and x.类型=t.类型
)
order by 类型,cnt desc
select * from (
select (select count(*) from
(select p.类型,p.ID,p.名称,count(*) as cnt
from 订单 o inner join 订单详细 i on o.订单号=i.订单号 and o.成功=1
inner join 产品 p on i.产品ID=p.ID group by p.类型,p.ID,p.名称
) b where b.cnt>=a.cnt and b.类型=a.类型
) as GroupID,
a.*
from
(select p.类型,p.ID,p.名称,count(*) as cnt
from 订单 o inner join 订单详细 i on o.订单号=i.订单号 and o.成功=1
inner join 产品 p on i.产品ID=p.ID
group by p.类型,p.ID,p.名称
) a
) c
where c.GroupID<=20
order by c.类型,c.GroupID
目前mysql没有类似这样的函数