【转载】sql 行专列经典

低效

 select host,max(h0), max(h1), max(h2), max(h3), max(h4) from (select 
tmp.host, 
(case tmp.hour when '00' then  if(tmp.count is null,0,1)  end) h0,
(case tmp.hour when '01' then tmp.count end) h1,
(case tmp.hour when '02' then tmp.count end) h2,
(case tmp.hour when '03' then tmp.count end) h3,
(case tmp.hour when '04' then tmp.count end) h4
from (select host,substr(ymdhms,9,2) as hour,
       count(*) as count 
      from cndnslogsec where dt = '20131220' and node='njtel' and host ='t.cn' and ymdhms is not null 
      group by host,substr(ymdhms,9,2) 
      ) tmp limit 10)  table1 group by host; 

高效：

  select host, 

count(*) as h,

sum (case substr(ymdhms,9,2)  when '00' then  1 else 0 end) as h0,

sum (case substr(ymdhms,9,2)  when '01' then  1 else 0 end) as h1, 

sum (case substr(ymdhms,9,2)  when '02' then  1 else 0 end) as h2,

sum (case substr(ymdhms,9,2)  when '03' then  1 else 0 end) as h3,

sum (case substr(ymdhms,9,2)  when '04' then  1 else 0 end) as h4,

sum (case substr(ymdhms,9,2)  when '05' then  1 else 0 end) as h5,

sum (case substr(ymdhms,9,2)  when '06' then  1 else 0 end) as h6,

sum (case substr(ymdhms,9,2)  when '07' then  1 else 0 end) as h7,

sum (case substr(ymdhms,9,2)  when '08' then  1 else 0 end) as h8,

sum (case substr(ymdhms,9,2)  when '09' then  1 else 0 end) as h9,

sum (case substr(ymdhms,9,2)  when '10' then  1 else 0 end) as h10,

sum (case substr(ymdhms,9,2)  when '11' then  1 else 0 end) as h11,

sum (case substr(ymdhms,9,2)  when '12' then  1 else 0 end) as h12,

sum (case substr(ymdhms,9,2)  when '13' then  1 else 0 end) as h13,

sum (case substr(ymdhms,9,2)  when '14' then  1 else 0 end) as h14,

sum (case substr(ymdhms,9,2)  when '15' then  1 else 0 end) as h15,

sum (case substr(ymdhms,9,2)  when '16' then  1 else 0 end) as h16,

sum (case substr(ymdhms,9,2)  when '17' then  1 else 0 end) as h17,

sum (case substr(ymdhms,9,2)  when '18' then  1 else 0 end) as h18,

sum (case substr(ymdhms,9,2)  when '19' then  1 else 0 end) as h19,

sum (case substr(ymdhms,9,2)  when '20' then  1 else 0 end) as h20,

sum (case substr(ymdhms,9,2)  when '21' then  1 else 0 end) as h21,

sum (case substr(ymdhms,9,2)  when '22' then  1 else 0 end) as h22,

sum (case substr(ymdhms,9,2)  when '23' then  1 else 0 end) as h23

from cndnslogsec where dt = '20131220' and node='njtel'  and ymdhms is not null 

group by host