SELECT *
FROM (SELECT u.GroupName,
Count(MONTH(r.AcctStartTime)) AS Jan,
Count(MONTH(r.AcctStartTime)) AS Feb,
Count(MONTH(r.AcctStartTime)) AS Mar,
Count(MONTH(r.AcctStartTime)) AS Apr,
Count(MONTH(r.AcctStartTime)) AS May,
Count(MONTH(r.AcctStartTime)) AS Jun,
Count(MONTH(r.AcctStartTime)) AS Jul,
Count(MONTH(r.AcctStartTime)) AS Aug,
Count(MONTH(r.AcctStartTime)) AS Sep,
Count(MONTH(r.AcctStartTime)) AS Oct,
Count(MONTH(r.AcctStartTime)) AS Nov,
Count(MONTH(r.AcctStartTime)) AS Dece
FROM radacct r ,usergroup u
where r.UserName = u.UserName
Group by u.GroupName) subq
ผมไม่แน่ใจว่าจะตอบยังไงดี ศึกษาเรื่อง case when ดูนะครับ
Date :
2009-04-03 00:06:01
By :
plakrim
No. 3
Guest
try on this:
select
user_name,
case dat
when '1' then 'Jan'
when '2' then 'Feb'
when '3' then 'Mar'
when '4' then 'Apr'
when '5' then 'May'
when '6' then 'Jun'
when '7' then 'Jul'
when '8' then 'Aug'
when '9' then 'Sep'
when '10' then 'Oct'
when '11' then 'Nov'
when '12' then 'Dece'
end 'date',
cnt
from
(
select UserName, dat, count(dat) as cnt
from
(
Select
UserName, MONTH(AcctStartTime) as dat
from radacct
where year(AcctStartTime) ='2008'
) a
group by UserName, dat
) as result order by 1, 2
select
username,
case dat
when '1' then 'Jan'
when '2' then 'Feb'
when '3' then 'Mar'
when '4' then 'Apr'
when '5' then 'May'
when '6' then 'Jun'
when '7' then 'Jul'
when '8' then 'Aug'
when '9' then 'Sep'
when '10' then 'Oct'
when '11' then 'Nov'
when '12' then 'Dece'
end 'date',
cnt
from
(
select UserName, dat, count(dat) as cnt
from
(
Select
UserName, MONTH(AcctStartTime) as dat
from radacct
where year(AcctStartTime) ='2008'
) a
group by UserName, dat
) as result order by 1, 2
Load balance : Server 04
