case when用法例子 SQL中的CASE WHEN使用方法(6)
B VIDEO
--------------------------------
通过一条SQL语句得出下列统计数据：
table2
username CALL_TIMES SEARCH_TIMES VIDEO_TIMES
-------------------------------------------------------------------------------
A 2 3 1
B 1 2 2
-------------------------------------------------------------------------------
说明：
table.action 中所有可能的值都已知的，只有 CALL , SEARCH ,VIDEO 和 table2 的字段一一对应
MYSQL 语句：
select
t.username,
max(CASE T.action WHEN 'CALL' THEN T.NN ELSE 0 end) as CALL_TIMES,
max(CASE T.action WHEN 'SEARCH' THEN T.NN ELSE 0 end) as SEARCH_TIMES,
max(CASE T.action WHEN 'VIDEO' THEN T.NN ELSE 0 end) as VIDEO_TIMES
from
(select temp.username,temp.action,count(*) as NN
from temp
group by temp.username,temp.action) as T
group by T.username
对应的 Oracle 语句:
select username,
max(decode(action,'CALL',NN,0)) AS CALL_TIMES ,
max(decode(action,'VIDEO',NN,0)) AS VIDEO_TIMES ,
max(decode(action,'SEARCH',NN,0)) AS VIDEO_TIMES
from
(select username,action,count(*) AS NN from table1
group by username,action)
group by username
