我有一张表:
col1 col2 col3
a 1 word
a 2 excel
a 3 ppt
a 4 windows
b 1 word
b 2 excel
b 3 windows
b 4 server
b 5 ppt
c 1 excel
c 2 word
c 3 server
c 4 ppt
c 5 excel
.............................各位老大, 我的意思是从a,b,c看一下第三列出现的顺序,找出一个大致的趋势,就是这些东西怎么排列的,从前到后用频率排一下,谢谢各位老大,在线等,急急急!
col1 col2 col3
a 1 word
a 2 excel
a 3 ppt
a 4 windows
b 1 word
b 2 excel
b 3 windows
b 4 server
b 5 ppt
c 1 excel
c 2 word
c 3 server
c 4 ppt
c 5 excel
.............................各位老大, 我的意思是从a,b,c看一下第三列出现的顺序,找出一个大致的趋势,就是这些东西怎么排列的,从前到后用频率排一下,谢谢各位老大,在线等,急急急!
insert into @t select
'a', 1, 'word' union all select
'a', 2, 'excel' union all select
'a', 3, 'ppt' union all select
'a', 4, 'windows' union all select
'b', 1, 'word' union all select
'b', 2, 'excel' union all select
'b', 3, 'windows' union all select
'b', 4, 'server' union all select
'b', 5, 'ppt' union all select
'c', 1, 'excel' union all select
'c', 2, 'word' union all select
'c', 3, 'server' union all select
'c', 4, 'ppt' union all select
'c', 5, 'excel'select col2,sum(case col3 when 'excel' then 1 else 0 end) excel,
sum(case col3 when 'ppt' then 1 else 0 end) ppt,sum(case col3 when 'server' then 1 else 0 end) server,
sum(case col3 when 'windows' then 1 else 0 end) windows,sum(case col3 when 'word' then 1 else 0 end) word
from @t group by col2
from tab
group by col3
order by sum(col2)
from tab
group by col3
order by sum(col2)/count(col2)