发个SQL 看看大家的SQL语句谁更简单
SELECT person_id, work_year FROM info_person ip

---结果 person_id int,work_year int
21100204
21100675
21122746
21137757
21138418
21145089
211487010
211533711
21154087
21154315
21156106

需要对不同工作年限的个人进行统计 如下：

工作年限 数量 占比
1 15000 15.00%
2 15222 16.00%

请给出你认为够简单的SQL语句！


[解决办法]

SQL code

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) 
 
[解决办法]
SQL code
select work_year as 工作年限 ,count(*) as 数量,convert(decimal(18,2),count(*)*100./(select count(*) from info_person)) as 所占比例 from   info_person group by work_year
[解决办法]
8楼一早都写出来了啊
[解决办法]
SQL code
select work_year as 工作年限 ,count(*) as 数量,cast(convert(decimal(18,2),count(*)*100/(select count(*) from info_person )) as varchar(10)) + '%' as 所占比例 from   info_person  group by work_year
[解决办法]
我觉得最简单的不是一条语句就完成，我觉得不论是程序还是sql语句，最清晰明确才是最重要的
如果你一条语句嵌套太多，看着都累
可以用几条语句来解决，一看就能让人看懂才是最重要的。
[解决办法]
Select work_year AS 工作年限,Count(work_year) AS 数量,(cast( Count(work_year) as decimal(18,5))/cast(Count(*) as decimal(18,5))) AS 占比 from info_person GROUP BY work_year