某个表数据如下:
A
A
A
B
B
B
B
C
C
D
有没有办法用sql写出一个语句,查询结果如下:
A 30%
B 40%
C 20%
D 10%
--
創建測試環境
Create Table test1
(
ftest Varchar ( 20 ))
Insert test1 Select ' A '
Union All Select ' A '
Union All Select ' A '
Union All Select ' B '
Union All Select ' B '
Union All Select ' B '
Union All Select ' B '
Union All Select ' C '
Union All Select ' C '
Union All Select ' D '
-- 查询
select ftest, 百分比 = cast ( cast ( count ( * ) * 100 . / ( select count ( * ) from test1) as decimal ( 10 , 0 )) as varchar ) + ' % '
from test1
group by ftest
go
-- 删除测试
drop table test1
Create Table test1
(
ftest Varchar ( 20 ))
Insert test1 Select ' A '
Union All Select ' A '
Union All Select ' A '
Union All Select ' B '
Union All Select ' B '
Union All Select ' B '
Union All Select ' B '
Union All Select ' C '
Union All Select ' C '
Union All Select ' D '
-- 查询
select ftest, 百分比 = cast ( cast ( count ( * ) * 100 . / ( select count ( * ) from test1) as decimal ( 10 , 0 )) as varchar ) + ' % '
from test1
group by ftest
go
-- 删除测试
drop table test1