表A cust_login_id(varchar 50),server_id(varchar 50),start_time,connect_time,endtime,product_id(int),status(varchar 50)
表A的数据
xx 18 .... .... .... 1 waiting
you 13.... .... ... 3 connting
dd 13 ............ 2 waiting
xx 18 ............. 2 end
表b,是product对于的表product_id.product_name,
表b的数据
1--ss
2--ww
3--jj
表c,是server的server_id, server_name , server_status
表c的数据
18--li -- on
13--zhang --off 需要实现以下结果
server_name | server_id|server_status | current_ Request| today_requset|ave.time|product_name
li -18 -on -5-14- 00:56:00- ss/ww
zhang -13 -off -0-29--01:28:00--ww/jj server_name | server_id|server_status 都是先判断表a中connect_time是今天,其对应server_id联系的表c的值,(server_name不要重复)
current_ Request是表A 中status为'CONNECTED',的cust_login_id个数
today_requset是表A中connect_time是今天,status为'end'的cust_login_id不重复个数
ave.time是先判断 表A中connect_time是今天,conert(varchar(8),dateadd(ss, sum (datediff(ss,START_TIME,End_time)的差值/today_requset
product_name 是server_name在表A中connect_time是今天的服务产品名 (所有产品用/连接)
表A的数据
xx 18 .... .... .... 1 waiting
you 13.... .... ... 3 connting
dd 13 ............ 2 waiting
xx 18 ............. 2 end
表b,是product对于的表product_id.product_name,
表b的数据
1--ss
2--ww
3--jj
表c,是server的server_id, server_name , server_status
表c的数据
18--li -- on
13--zhang --off 需要实现以下结果
server_name | server_id|server_status | current_ Request| today_requset|ave.time|product_name
li -18 -on -5-14- 00:56:00- ss/ww
zhang -13 -off -0-29--01:28:00--ww/jj server_name | server_id|server_status 都是先判断表a中connect_time是今天,其对应server_id联系的表c的值,(server_name不要重复)
current_ Request是表A 中status为'CONNECTED',的cust_login_id个数
today_requset是表A中connect_time是今天,status为'end'的cust_login_id不重复个数
ave.time是先判断 表A中connect_time是今天,conert(varchar(8),dateadd(ss, sum (datediff(ss,START_TIME,End_time)的差值/today_requset
product_name 是server_name在表A中connect_time是今天的服务产品名 (所有产品用/连接)
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id value
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加)
cust_loginid|server_id|start_time|connect_time|endtime|product_id|status
数据如下
xx |18| 2008-3-18 17:07:21|2009-3-19 23:00:00|2008-3-18 17:08:23 | 1 |waiting
you |13|2008-3-18 17:07:21|2009-3-19 23:00:00|2008-3-18 17:08:23 |3 |connting
dd | 13 |2008-3-18 17:07:21|2009-3-19 23:00:00|2008-3-18 17:08:23 | 2| waiting
xx | 18 |2008-3-18 17:07:21|2009-3-19 23:00:00|2008-3-18 17:08:23 | 2| end
....
表b,是product对于的表product_id.product_name,
product_id|product_name
1|ss
2|ww
3|jj
表c,是server的server_id, server_name , server_status
server_id|server_name | server_status
18--li -- on
13--zhang --off
需要实现以下结果
server_name | server_id|server_status | current_ Request| today_requset|ave.time|product_name
li -18 -on -0-1- 00:56:00- ss/ww
zhang -13 -off -1-0--01:28:00--ww/jj where connect_time是当天
即 获取connect_time是当天的server_name 的信息,server_name不要重复,
current_ Request是server_name现在表A中今天status为'CONNECTED',的cust_login_id个数(可以重复)
today_requset是表A中,今天status为'end'的cust_login_id重复个数
ave.time是今天(datediff(ss,START_TIME,End_time)的差值/today_requset
product_name 是server_name在表A中今天的服务产品名 (所有产品用/连接)
create table A(
cust_login_id varchar(20),server_id varchar(20),start_time datetime,
connect_time datetime,end_time datetime,product_id int,status varchar(20))
create table B(product_id int,product_name varchar(20))
create table C(server_id int,server_name varchar(20),server_status varchar(10))insert into A select 'xx ',18,dateadd(mi,20,getdate()),getdate(),getdate(),1,'waiting '
insert into A select 'you',13,dateadd(mi,20,getdate()),getdate(),getdate(),3,'connting'
insert into A select 'dd ',13,dateadd(mi,20,getdate()),getdate(),getdate(),2,'waiting '
insert into A select 'xx ',18,dateadd(mi,20,getdate()),getdate(),getdate(),2,'end 'insert into B select 1,'ss'
insert into B select 2,'ww'
insert into B select 3,'jj' insert into C select 18,'li ','on '
insert into C select 13,'zhang','off'
gocreate function f_str(@server_id int)
returns varchar(40)
as
begin
declare @ret varchar(40)
select
@ret=isnull(@ret,'')+'/'+product_name
from
(select
distinct B.product_name
from
A,B
where
A.product_id=B.product_id and A.server_id=@server_id) t
set @ret=stuff(@ret,1,1,'')
return @ret
end
goselect
t.server_name ,
t.server_id ,
t.Request ,
t.connect_time,
[ave.time]=(t.connect_time/(case when t.Request=0 then 1 else t.Request end)),
t.product_name
from
(select
C.server_name ,
C.server_id ,
C.server_status,
Request =sum(case when A.status in('CONNECTED','waiting') then 1 else 0 end),
connect_time =sum(case when A.status='CONNECTED' then datediff(mi,A.START_TIME,A.END_TIME) else 0 end),
dbo.f_str(C.server_id) as 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,C.server_status) t
order by
t.server_name,t.server_id/*
server_name server_id Request connect_time ave.time product_name
-------------------- ----------- ----------- ------------ ----------- ----------------------------------------
li 18 1 0 0 ss/ww
zhang 13 1 0 0 jj/ww
*/
godrop function f_str
drop table A,B,C
go
insert into @表a
select 'xx',18,'2008-3-18 17:07:21','2009-3-19 23:00:00','2008-3-18 17:08:23',1,'waiting' union all
select 'you',13,'2008-3-18 17:07:21','2009-3-19 23:00:00','2008-3-18 17:08:23',3,'connting' union all
select 'dd',13,'2008-3-18 17:07:21','2009-3-19 23:00:00','2008-3-18 17:08:23',2,'waiting' union all
select 'xx',18,'2008-3-18 17:07:21','2009-3-19 23:00:00','2008-3-18 17:08:23',2,'end'select * from @表a
declare @表b table (product_id int,product_name varchar(2))
insert into @表b
select 1,'ss' union all
select 2,'ww' union all
select 3,'jj'select * from @表b
declare @表c table (server_id int,server_name varchar(5),server_status varchar(3))
insert into @表c
select 18,'li','on' union all
select 13,'zhang','off'select * from @表c/*
cust_loginid server_id start_time connect_time endtime product_id status
------------ ----------- ----------------------- ----------------------- ----------------------- ----------- --------
xx 18 2008-03-18 17:07:21.000 2009-03-19 23:00:00.000 2008-03-18 17:08:23.000 1 waiting
you 13 2008-03-18 17:07:21.000 2009-03-19 23:00:00.000 2008-03-18 17:08:23.000 3 connting
dd 13 2008-03-18 17:07:21.000 2009-03-19 23:00:00.000 2008-03-18 17:08:23.000 2 waiting
xx 18 2008-03-18 17:07:21.000 2009-03-19 23:00:00.000 2008-03-18 17:08:23.000 2 endproduct_id product_name
----------- ------------
1 ss
2 ww
3 jjserver_id server_name server_status
----------- ----------- -------------
18 li on
13 zhang off*/
create table B(product_id int,product_name varchar(20))
create table C(server_id int,server_name varchar(20),server_status varchar(10))
insert into A select 'xx ',18,dateadd(mi,-20,getdate()),getdate(),getdate(),1,'waiting '
insert into A select 'you',13,dateadd(mi,-20,getdate()),getdate(),getdate(),3,'connting'
insert into A select 'dd ',13,dateadd(mi,-20,getdate()),getdate(),getdate(),2,'waiting '
insert into A select 'xx ',18,dateadd(mi,-20,getdate()),getdate(),getdate(),2,'end '
insert into B select 1,'ss'
insert into B select 2,'ww'
insert into B select 3,'jj'
insert into C select 18,'li ','on '
insert into C select 13,'zhang','off'
gocreate function dbo.f_str(@server_id varchar(20)) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(product_name as varchar) from (select a.server_id , b.product_name from a , b where a.product_id = b.product_id ) t where server_id = @server_id
set @str = right(@str , len(@str) - 1)
return @str
end
goselect m.* , n.product_name from
(
select c.server_name ,
c.server_id ,
c.server_status ,
current_Request = (select count(1) from a where datediff(day , connect_time,getdate()) = 0 and a.server_id = c.server_id and a.status='CONNECTED'),
today_requset = (select count(distinct cust_login_id) from a where datediff(day , connect_time,getdate()) = 0 and a.server_id = c.server_id and a.status='end'),
[ave.time] = (case when (select count(distinct cust_login_id) from a where datediff(day , connect_time,getdate()) = 0 and a.server_id = c.server_id and a.status='end') = 0 then 0 else (select sum(datediff(ss,START_TIME,End_time)) from a where datediff(day , connect_time,getdate()) = 0 and a.server_id = c.server_id )/(select count(distinct cust_login_id) from a where datediff(day , connect_time,getdate()) = 0 and a.server_id = c.server_id and a.status='end') end)
from c
) m,
(
select server_id , product_name = dbo.f_str(server_id) from (select a.server_id , b.product_name from a , b where a.product_id = b.product_id and datediff(day,a.connect_time,getdate()) = 0) t group by server_id
) n
where m.server_id = n.server_iddrop function dbo.f_str
drop table a , b, c/*
server_name server_id server_status current_Request today_requset ave.time product_name
-------------------- ----------- ------------- --------------- ------------- ----------- ---------------
li 18 on 0 1 2400 ss,ww
zhang 13 off 0 0 0 ww,jj(所影响的行数为 2 行)*/