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,

时间单位是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_statusset  wpp_state = 1,wpp_out_time = 129384848where  wpp_sn='XBX20091022' and  wpp_class='ABC' and  wpp_state = 0update  table_wpp_statusset  wpp_state = 1,wpp_out_time = 129384848where   wpp_sn='XBX20091023' and  wpp_class='ABC' and  wpp_state = 0update  table_wpp_statusset  wpp_state = 1,wpp_out_time = 129384848where  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_outputfrom  table_wpp_status as s1where  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 >= 129382848group by  s1.wpp_emp_IDorder by  total_output desclimit  10;

输出结果：

emp_ID     total_input      total_outputPerry3     2                2Perry2     1                1Perry1     1                0

总结

以上是内存溢出为你收集整理的SQLite3中按时间范围分类排名(top 10)全部内容，希望文章能够帮你解决SQLite3中按时间范围分类排名(top 10)所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。