SELECT TOP 20
g.no
,g.name
,m.num
,row_number()over(order by m.num desc) as gid --伪序列号
FROM
(
SELECT no, name FROM tb1
) g
INNER JOIN
(
SELECT no, COUNT(*) AS num FROM tb2
GROUP BY no
) m
ON g.no = m.o
优化前用时20ms
优化后
select top20 row_number() over (order by getdate()) as gid, g.no,g.name,count(m.no) as num from tb1 as g join tb2 as m on g.no =m.no group by g.no,g.name order by num desc
用时3ms
