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',129384821,0)

insert into
  table_wpp_status(
    wpp_sn,wpp_out_time)
values
  ('XBX20091022','Perry2',129384824,wpp_out_time)
values
  ('XBX20091023','Perry3',129384829,wpp_out_time)
values
  ('XBX20091024',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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!