SQL转换结果集（一）

表app_online_time，结构如下：

drop table if exists app_online_time;
create table app_online_time (
  id int(11) not null auto_increment,
  app_codename varchar(40),                -- 应用名称
  user_id int(11),                        -- 用户id
  connect_times int(11),                -- 在线时长(分)
  record_date varchar(20),                -- 记录时间
  primary key (id)
); 

 

-- 插入数据 

insert into app_online_time (app_codename, user_id, connect_times, record_date) values
('Garden', 1401578, 1, '2010-01-01 00:00:18'),
('Saol', 1401578, 1, '2010-01-01 00:00:18'),
('Ddz', 1520090, 1, '2010-01-01 00:00:18'),
('Saol', 1448163, 4, '2010-01-02 00:00:18'),
('Garden', 1247744, 1, '2010-01-02 00:01:19'),
('Saol', 1535748, 32, '2010-01-02 00:01:19'),
('Saol', 1227407, 10, '2010-01-03 00:01:19'),
('Saol', 1274067, 4, '2010-01-03 00:01:19'),
('Ddz', 1537307, 3, '2010-01-03 00:03:22'),
('Ddz', 1380496, 10, '2010-01-04 00:06:24'),
('Garden', 1464414, 2, '2010-01-04 00:19:32'),
('Ddz', 1537537, 8, '2010-01-04 04:20:33'),
('Garden', 1533676, 1, '2010-01-05 00:19:32'),
('Garden', 1390241, 28, '2010-01-05 00:20:33');

 

问题：列出每一天每个应用的在线人数

-- step 1：求出每天各个应用的在线人数
select left(record_date, 10) online_date, app_codename, count(distinct user_id) online_users
  from app_online_time  
 where record_date >= '2010-01-01' and record_date < adddate('2010-01-05', interval 1 day)
 group by left(record_date, 10), app_codename;

 

--  step 2： 将上述结果作为内联视图进行转换

select x.online_date, max(case when app_codename = 'Ddz' then x.online_users else 0 end) 'Ddz',
    max(case when app_codename = 'Garden' then x.online_users else 0 end) 'Garden',
    max(case when app_codename = 'Saol' then x.online_users else 0 end) 'Saol'
  from (
 select left(record_date, 10) online_date, app_codename, count(distinct user_id) online_users
   from app_online_time  
  where record_date >= '2010-01-01' and record_date < adddate('2010-01-05', interval 1 day)
  group by left(record_date, 10), app_codename) x
 group by x.online_date;