表A cust_login_id(varchar 50),server_id(varchar 50),start_time,connect_time,endtime,product_id(int),status(varchar 50)
表b,是product对于的表product_id.product_name,
表c,是server的server_id, server_nameserver_name | server_id | Request| connect_time|ave.time|product_name
的数据
sunny|123|4|23:56:00|6:00:00| ss
sunny|123|4|23:56:00|6:00:00| ww Request是表a的status为'CONNECTED','waiting'的cust_login_id个数 connect_time是表a的status为'CONNECTED',datediff(minute,START_TIME,End_time)的和
ave.time=connect_time/Request
表b
1--ss
2--ww
表a
xx 18 .... .... .... 1 waiting
you 18 .... .... ... 1 connting
dd 13 ............ 2 waiting
表b,是product对于的表product_id.product_name,
表c,是server的server_id, server_nameserver_name | server_id | Request| connect_time|ave.time|product_name
的数据
sunny|123|4|23:56:00|6:00:00| ss
sunny|123|4|23:56:00|6:00:00| ww Request是表a的status为'CONNECTED','waiting'的cust_login_id个数 connect_time是表a的status为'CONNECTED',datediff(minute,START_TIME,End_time)的和
ave.time=connect_time/Request
表b
1--ss
2--ww
表a
xx 18 .... .... .... 1 waiting
you 18 .... .... ... 1 connting
dd 13 ............ 2 waiting
select
t.server_name ,
t.server_id ,
t.Request ,
t.connect_time,
[ave.time]=(t.connect_time/(case then t.Request=0 then 1 else t.Request end)),
t.product_name
from
(select
C.server_name ,
C.server_id ,
Request =sum(case when A.status in('CONNECTED','waiting') then 1 esle 0 end),
connect_time =sum(case when A.status='CONNECTED' then datediff(mi,A.START_TIME,A,END_TIME) else 0 end),
B.product_name
from
C,B,A
where
C.server_id=A.server_id and B.product_id=A.product_id
group by
C.server_name,C.server_id,B.product_name) t
order by
t.server_name,t.server_id,t.product_name