SQLite3中按时间范围分类排名(top 10)

时间单位是C#中的DateTime.ToFileTime() / 1000000000

 

创建表：

create table
  if not exists
    table_wpp_status(
      wpp_id integer primary key autoincrement not null, 
      wpp_sn varchar(64) not null, 
      wpp_emp_id varchar(32) not null, 
      wpp_class varchar(32) not null, 
      wpp_state integer default(0), 
      wpp_in_time integer default(0), 
      wpp_out_time integer default(0))

插入表数据：

insert into
  table_wpp_status(
    wpp_sn,
    wpp_emp_id,
    wpp_class,
    wpp_state,
    wpp_in_time,
    wpp_out_time)
values
  ('XBX20091021',
   'Perry1',
   'ABC',
   0,
   129384821,
   0)

insert into
  table_wpp_status(
    wpp_sn,
    wpp_emp_id,
    wpp_class,
    wpp_state,
    wpp_in_time,
    wpp_out_time)
values
  ('XBX20091022',
   'Perry2',
   'ABC',
   0,
   129384824,
   0)

insert into
  table_wpp_status(
    wpp_sn,
    wpp_emp_id,
    wpp_class,
    wpp_state,
    wpp_in_time,
    wpp_out_time)
values
  ('XBX20091023',
   'Perry3',
   'ABC',
   0,
   129384829,
   0)

insert into
  table_wpp_status(
    wpp_sn,
    wpp_emp_id,
    wpp_class,
    wpp_state,
    wpp_in_time,
    wpp_out_time)
values
  ('XBX20091024',
   'Perry3',
   'ABC',
   0,
   129384830,
   0)

修改数据：

update
  table_wpp_status
set
  wpp_state = 1,
  wpp_out_time = 129384848
where
  wpp_sn='XBX20091022' and
  wpp_class='ABC' and
  wpp_state = 0

update
  table_wpp_status
set
  wpp_state = 1, 
  wpp_out_time = 129384848
where 
  wpp_sn='XBX20091023' and
  wpp_class='ABC' and
  wpp_state = 0

update
  table_wpp_status
set
  wpp_state = 1,
  wpp_out_time = 129384848
where
  wpp_sn='XBX20091024' and
  wpp_class='ABC' and
  wpp_state = 0

查询数据：

select
  s1.wpp_emp_id as emp_id,
  count(s1.wpp_id) as total_input,
  count
  ( case when
      s1.wpp_state >= 1 and
      s1.wpp_out_time >= 129382848
    then
      s1.wpp_id
    end) as total_output
from
  table_wpp_status as s1
where
  s1.wpp_class = 'ABC' and
  s1.wpp_emp_id in
  ( select
      distinct s2.wpp_emp_id
    from
      table_wpp_status as s2
    where
      s2.wpp_in_time >= 129382848) and
  s1.wpp_in_time >= 129382848
group by
  s1.wpp_emp_id
order by
  total_output desc
limit
  10;

输出结果：

emp_id     total_input      total_output
Perry3     2                2
Perry2     1                1
Perry1     1                0