有3个表:t_online_information(switch_numport_num,record_time,mac_address,ip_address),
t_switch_staff_information(switch_num,port_num,staff_station),
t_staff_information(staff_station,staff_name,cellphone,email,department,floors)
在页面中要显示
工位 员工姓名 部门 所在楼层 联系方式 未关机次数 6个字段
(staff_station)(staff_name)(department)(floors)(cellphone) (需要在SQL语句中定义)
其中未关机次数是根据表t_online_information来确定,方法:由record_time,mac_address,ip_address3个字段决定,例如某个ip的record_time为2010-6-22 02:00:00,则说明该ip凌晨在线,需要统计该ip一个星期凌晨在线的次数。
在页面中按照未关机次数的降序显示。感觉难点在:未关机次数没有对应的数据字段,而且与页面显示的其它数据字段不在一个表中,我试了试,count函数只能返回一个值,但我这里好像要统计表t_online_information里所有ip凌晨在线的次数。
t_switch_staff_information(switch_num,port_num,staff_station),
t_staff_information(staff_station,staff_name,cellphone,email,department,floors)
在页面中要显示
工位 员工姓名 部门 所在楼层 联系方式 未关机次数 6个字段
(staff_station)(staff_name)(department)(floors)(cellphone) (需要在SQL语句中定义)
其中未关机次数是根据表t_online_information来确定,方法:由record_time,mac_address,ip_address3个字段决定,例如某个ip的record_time为2010-6-22 02:00:00,则说明该ip凌晨在线,需要统计该ip一个星期凌晨在线的次数。
在页面中按照未关机次数的降序显示。感觉难点在:未关机次数没有对应的数据字段,而且与页面显示的其它数据字段不在一个表中,我试了试,count函数只能返回一个值,但我这里好像要统计表t_online_information里所有ip凌晨在线的次数。
select staff_station,staff_name,cellphone,department,floors,ip_address,count(1) as 未关机次数
from t_staff_information a left join t_switch_staff_information b
on a.staff_station = b.staff_station
left join t_online_information c
on b.switch_num=c.switch_numport_num
where datepart(hour,record_time) between 1 and 3
group by staff_station,staff_name,cellphone,department,floors,ip_address
order by count(1) desc
DECLARE @t_online_information TABLE(switch_num INT, port_num INT, record_time DATETIME, mac_address VARCHAR(100), ip_address VARCHAR(100))
DECLARE @t_switch_staff_information TABLE (switch_num INT, port_num INT, staff_station VARCHAR(100))
DECLARE @t_staff_information TABLE(staff_station VARCHAR(100), staff_name VARCHAR(100), cellphone VARCHAR(100), email VARCHAR(100), department VARCHAR(100),floors VARCHAR(10))INSERT INTO @t_staff_information( staff_station , staff_name , cellphone , email , department , floors )
VALUES ( 'staff_stationA' , 'A' , 'cellphoneA' , 'emailA' , 'departmentA' , 'floorsA')
INSERT INTO @t_staff_information( staff_station , staff_name , cellphone , email , department , floors )
VALUES ( 'staff_stationB' , 'B' , 'cellphoneB' , 'emailB' , 'departmentB' , 'floorsB')INSERT INTO @t_switch_staff_information ( switch_num , port_num , staff_station)
VALUES ( 0 , 0 , 'staff_stationA')
INSERT INTO @t_switch_staff_information ( switch_num , port_num , staff_station)
VALUES ( 1 , 1 , 'staff_stationB')INSERT INTO @t_online_information(switch_num , port_num , record_time , mac_address , ip_address)
VALUES ( 0 , 0 , '2010-06-21 1:31:15' , 'mac_address1' , 'ip_address1')
INSERT INTO @t_online_information(switch_num , port_num , record_time , mac_address , ip_address)
VALUES ( 0 , 0 , '2010-06-20 2:31:15' , 'mac_address1' , 'ip_address1')SELECT A.staff_station,A.staff_name,A.cellphone,A.department,A.floors,C.ip_address,COUNT(C.switch_num) AS 未关机次数
FROM @t_staff_information A
LEFT JOIN @t_switch_staff_information B ON A.staff_station = B.staff_station
LEFT JOIN @t_online_information C ON B.switch_num = C.switch_num
AND B.port_num = C.port_num
AND DATEPART(HOUR,C.record_time) BETWEEN 1 AND 3 -- 凌晨
AND C.record_time BETWEEN '2010-06-20' AND '2010-06-26 23:59:59.997' -- 本周
GROUP BY A.staff_station,A.staff_name,A.cellphone,A.department,A.floors,C.ip_address
ORDER BY 未关机次数 DESC
FROM t_staff_information2 as A
INNER JOIN t_switch_staff_information2 as B ON A.staff_station = B.staff_station
INNER JOIN t_online_information2 as C ON B.switch_num = C.switch_num and B.port_num = C.port_num
where DATEPART(HOUR,record_time)=2 and DATEPART(minute,record_time)=0 and DATEPART(wk,record_time)=DATEPART(wk,GETDATE()) and DATEPART(yy, record_time)=DATEPART(yy, GETDATE())
GROUP BY A.staff_station,A.staff_name,A.cellphone,A.department,A.floors
select A.staff_station,A.staff_name,A.cellphone,A.department,A.floors,count(C.ip_address) as num
FROM t_staff_information2 as A
INNER JOIN t_switch_staff_information2 as B ON A.staff_station = B.staff_station
INNER JOIN t_online_information2 as C ON B.switch_num = C.switch_num and B.port_num = C.port_num
where DATEPART(HOUR,record_time)=2 and DATEPART(minute,record_time)=0 and DATEPART(wk,record_time)=DATEPART(wk,GETDATE()) and DATEPART(yy, record_time)=DATEPART(yy, GETDATE())
GROUP BY A.staff_station,A.staff_name,A.cellphone,A.department,A.floors