发个SQL 看看大家的SQL语句哪位高手更简单

select count（*） from info_person where work_year =“+work_year +”
------解决方案--------------------------------------------------------
写到存储过程中吧，先获得sum值，然后select xxx / sum值
------解决方案--------------------------------------------------------
还是写个存储过程比较好，把所有的工作年龄遍历一遍
------解决方案--------------------------------------------------------
SQL code
     Select work_year AS 工作年限,Count(work_year) AS 数量,(Count(work_year)/Count(*)) AS 占比 from info_person GROUP BY work_year
------解决方案--------------------------------------------------------
select work_year,count(*),count(*)*1./num from  
  (select *,count(*)over() from info_person) t group by work_year,num
------解决方案--------------------------------------------------------

SQL code
 Select work_year AS 工作年限,Count(work_year) AS 数量,(Count(work_year)/Count(*)) AS 占比 from info_person GROUP BY work_year
------解决方案--------------------------------------------------------
你这num是什么，根本不识别。

select work_year,count(work_year) as nmber, (COUNT(work_year)/(select COUNT(*) from info_person )) as rate from info_person ip  
group by work_year

比率怎么算啊
------解决方案--------------------------------------------------------
SQL code
Select work_year,Count(work_year) AS Quantity,STR(Convert(Decimal(10,2),(Count(work_year)*100.0/(Select Count(person_id) from info_person))),10,2)+'%' AS Proprotion from info_person Group by work_year
------解决方案--------------------------------------------------------
探讨


引用:

SQL code
Select work_year,Count(work_year) AS Quantity,STR(Convert(Decimal(10,2),(Count(work_year)*100.0/(Select Count(person_id) from info_person))),10,2)+'%' AS Proprotion from in……

------解决方案--------------------------------------------------------
over函数
SQL code
 CREATE TABLE  WBT(    person_id int,    work_year  int)INSERT INTO WBTSELECT 2110020,5 UNION ALL SELECT 2112274,6 UNION ALL SELECT 2113775,7 UNION ALL SELECT 2113841,8 UNION ALL SELECT 2114508,9 UNION ALL SELECT 2114870,10 UNION ALL SELECT 2115337,11 UNION ALL SELECT 2115408,7 UNION ALL SELECT 2115431,5 UNION ALL SELECT 2115610,6 select work_year as 工作年限 ,count(*) as 数量,convert(nvarchar(10),convert(decimal(18,2),count(*)*100.00/rowno))+'%' as 所占比例 from   (select *,count(*)over() rowno from WBT) t group by work_year,rowno /* 工作年限        数量          所占比例----------- ----------- -----------5           2           20.00%6           2           20.00%7           2           20.00%8           1           10.00%9           1           10.00%10          1           10.00%11          1           10.00%(7 行受影响 */
------解决方案--------------------------------------------------------
改了一下：
SQL code
create table #temp(    person_id int,    work_year int)insert into #temp(person_id,work_year)select 2110020 ,4  unionselect 2110067 ,5  unionselect 2112274 ,6  unionselect 2113775 ,7  unionselect 2113841 ,8  unionselect 2114508 ,9  unionselect 2114870 ,10 unionselect 2115337 ,11 unionselect 2115408 ,7  unionselect 2115431 ,5 unionselect 2115432 ,6;select distinct work_year as 工作年限,    count(work_year) over(partition by work_year) as 工作年限人数,    count(person_id) over () 总人数,    cast(        cast(            (count(work_year) over(partition by work_year))            /            cast((count(person_id) over ()) as decimal(18,2))*100  as int)         as varchar(50)    ) + '%' as 占比from #temporder by 工作年限truncate table #tempdrop table #temp(11 row(s) affected)工作年限        工作年限人数      总人数         占比----------- ----------- ----------- ---------------------------------------------------4           1           11          9%5           2           11          18%6           2           11          18%7           2           11          18%8           1           11          9%9           1           11          9%10          1           11          9%11          1           11          9%(8 row(s) affected)