有一个系统日志表syslogSEQ_NO LOG_DATE LOG_event LOG_CONTENT OPERAT
------------ ---------- ---- -------------------- ------------------------
000000000001 08-6月 -03 08:01:00 0000 03000008 01
000000000001 08-6月 -03 08:03:00 6101 03000009 01
000000000001 09-6月 -03 08:05:00 6101 03000009 01
000000000001 09-6月 -03 08:08:00 6102 03000010 01
000000000001 09-6月 -03 08:11:00 6102 03000009 01
000000000001 09-6月 -03 08:14:00 9999 03000010 01
000000000002 09-6月 -03 09:08:00 0000 03000010 01
000000000002 09-6月 -03 09:11:00 6102 03000009 01
000000000002 09-6月 -03 09:14:00 9999 03000010 01
log_event字段内容:
0000:登录 9999:退出 6101:录入 6102:修改
每次从登录到退出期间操作会用一个seq_no,
seq_no+log_date为主键希望统计结果为 操作员工作量统计表统计时间从:2003-06-01 到:2003-06-08 操作员:01登录时间 录入档案 修改档案 退出时间
----------------------------------------------------------------------
2003-06-08 08:01:00 2 2 2003-06-08 08:14:00
2003-06-08 09:08:00 2 2 2003-06-08 09:14:00 请高手指点怎样写简洁的SQL语句
------------ ---------- ---- -------------------- ------------------------
000000000001 08-6月 -03 08:01:00 0000 03000008 01
000000000001 08-6月 -03 08:03:00 6101 03000009 01
000000000001 09-6月 -03 08:05:00 6101 03000009 01
000000000001 09-6月 -03 08:08:00 6102 03000010 01
000000000001 09-6月 -03 08:11:00 6102 03000009 01
000000000001 09-6月 -03 08:14:00 9999 03000010 01
000000000002 09-6月 -03 09:08:00 0000 03000010 01
000000000002 09-6月 -03 09:11:00 6102 03000009 01
000000000002 09-6月 -03 09:14:00 9999 03000010 01
log_event字段内容:
0000:登录 9999:退出 6101:录入 6102:修改
每次从登录到退出期间操作会用一个seq_no,
seq_no+log_date为主键希望统计结果为 操作员工作量统计表统计时间从:2003-06-01 到:2003-06-08 操作员:01登录时间 录入档案 修改档案 退出时间
----------------------------------------------------------------------
2003-06-08 08:01:00 2 2 2003-06-08 08:14:00
2003-06-08 09:08:00 2 2 2003-06-08 09:14:00 请高手指点怎样写简洁的SQL语句
from
(select SEQ_NO, LOG_DATE as 登录时间 from syslog group by SEQ_NO where OPERAT = '01' and LOG_event = '0000') a,
(select SEQ_NO, count(*) as 录入档案 from syslog group by SEQ_NO where OPERAT = '01' and LOG_event = '6101') b,
(select SEQ_NO, count(*) as 修改档案 from syslog group by SEQ_NO where OPERAT = '01' and LOG_event = '6102') c,
(select SEQ_NO, LOG_DATE as 退出时间 from syslog group by SEQ_NO where OPERAT = '01' and LOG_event = '9999') d
where a.SEQ_NO = b.SEQ_NO and a.SEQ_NO= c.SEQ_NO and a.SEQ_NO = d.SEQ_NO
FROM
(select seq_no,logdate from syslog where log_event='0000' and convert(char(10),log_date,121)>='2003-06-01' and convert(char(10),log_date,121)<='2003-06-08') as A
left join (select seq_no,count(*) as addrec from syslog where log_event='6101' and convert(char(10),log_date,121)>='2003-06-01' and convert(char(10),log_date,121)<='2003-06-08' group by seq_no) as B on A.seq_no=B.seq_no
left join (select seq_no,count(*) as addrec from syslog where log_event='6101' and convert(char(10),log_date,121)>='2003-06-01' and convert(char(10),log_date,121)<='2003-06-08' group by seq_no) as C on A.seq_no=C.seq_no
left join (select seq_no,logdate from syslog where log_event='9999' and convert(char(10),log_date,121)>='2003-06-01' and convert(char(10),log_date,121)<='2003-06-08') as D on A.seq_no=D.seq_no
呵呵,還真是復雜,我現在機器上沒裝SQL.沒辦法運行,你自己可能要改改
~~~~~~~