如何替换sql 4.9.5版本中的Over子句

Question

我正在尝试运行一个查询，以查找基于性别的手工艺人的计数和百分比。查询在SQL server 8上运行得非常好。但不是在我的实时服务器上，它是4.9.5。下面是我的问题。

SELECT industry
, count(*) as cnt
, (count(*)*100.0/sum(count(*))over()) as perc 
FROM `artisan_work` 
GROUP BY industry 
ORDER BY cnt DESC 

Answer 1

在任何数据库中，您都应该能够使用：

SELECT aw.industry, count(*) as cnt,
count(*) * 100.0 / tot.cnt as perc 
FROM artisan_work aw cross join
(SELECT COUNT(*) as cnt FROM artisan_work) tot
GROUP BY aw.industry 
ORDER BY cnt DESC 

Answer 2

SELECT industry
, count(*) as cnt
, (count(*)*100.0/(select count(*) from artisan_work )) as perc 
FROM artisan_work  a
GROUP BY industry 
ORDER BY cnt DESC