表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_name , server_status
表a
xx 18 .... .... .... 1 waiting
you 13.... .... ... 3 connting
dd 13 ............ 2 waiting
xx 18 ............. 2 end表b
1--ss
2--ww
3--jj表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/jjserver_name | server_id|server_status 都是表c
current_ Request是表A 中status为'CONNECTED',的cust_login_id个数
today_requset是表A中connect_time是今天,status为'end'的cust_login_id不重复个数
ave.time是 表A中connect_time是今天,datediff(minute,START_TIME,End_time)的差值/today_requset
product_name 是server_name在表A中connect_time是今天的产品名 (所有产品用/连接)
表b,是product对于的表product_id.product_name,
表c,是server的server_id, server_name , server_status
表a
xx 18 .... .... .... 1 waiting
you 13.... .... ... 3 connting
dd 13 ............ 2 waiting
xx 18 ............. 2 end表b
1--ss
2--ww
3--jj表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/jjserver_name | server_id|server_status 都是表c
current_ Request是表A 中status为'CONNECTED',的cust_login_id个数
today_requset是表A中connect_time是今天,status为'end'的cust_login_id不重复个数
ave.time是 表A中connect_time是今天,datediff(minute,START_TIME,End_time)的差值/today_requset
product_name 是server_name在表A中connect_time是今天的产品名 (所有产品用/连接)
,[ave.time] = (datediff(minute,START_TIME,End_time)/today_requset)
from
(
select c.server_name,c.server_id,c.server_status
, current_Request = (select count(1) from 表A where status = 'CONNECTED')
, today_requset= (select count(1) from 表A where status = 'end' and datediff(dd,connect_time,getdate()) = 0)
,product_name = stuff((select ','+product_name from 表A a join 表B b on a.product_id = b.product_id and datediff(dd,a.connect_time,getdate()) = 0 for xml path('')),1,1,'')
from 表A a join 表B b on a.product_id = b.product_id
join 表C c on a.server_id = c.server_id
)t
其次:你的描述从来没清楚过,包括这次.
最后:猜测你的意思,是要合并某个字符串,参考如下的示例./*
标题:按某字段合并字符串之一(简单合并)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-11-06
地点:广东深圳描述:将如下形式的数据按id字段合并value字段。
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 的和(字符串相加)
*/
--1、sql2000中只能用自定义的函数解决
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
gocreate function dbo.f_str(@id int) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return @str
end
go--调用函数
select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_str
drop table tb
--2、sql2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by iddrop table tb
--3、使用游标合并数据
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
declare @t table(id int,value varchar(100))--定义结果集表变量
--定义游标并进行合并处理
declare my_cursor cursor local for
select id , value from tb
declare @id_old int , @id int , @value varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @value
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
if @id = @id_old
select @s = @s + ',' + cast(@value as varchar)
else
begin
insert @t values(@id_old , stuff(@s,1,1,''))
select @s = ',' + cast(@value as varchar) , @id_old = @id
end
fetch my_cursor into @id , @value
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursorselect * from @t
drop table tb
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
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 行)*/