******搞不定要失业了,快救我,急死了,分不够另开帖加,多表查询问题******
对应关系，一个部门对应多个用户，通过Work_group关联，一个用户对应多个经销商，通过tbl_Work的Work_code字段和tbl_dealer的cSales_Code字段关联，经销商在线记录表tbl_dealer_onlinetime，每个经销商都有每天的在线记录，他们通过cDealer_Code字段关联
用户部门表 tbl_WorkSubCity
Work_group Work_group_Name
01 北京
02 上海
03 深圳
用户表 tbl_Work
Work_code Work_group Work_name
1000 01 海淀
1001 01 朝阳
1002 02 长宁
1003 02 黄埔
1004 03 福田
1005 03 蛇口
经销商表 tbl_dealer
cDealer_Code cDealer_Name cSales_Code clevel(级别)
10000 网易 1000 a
10001 新浪 1000 a
10002 华为 1001 b
10003 腾讯 1001 b
经销商在线信息表 tbl_dealer_onlinetime
sid cdealer_code iminute(在线时长) creg_date
1 10000 1000 2007-05-10
2 10000 1100 2007-05-11
3 10000 1310 2007-05-12
4 10001 2000 2007-05-10
5 10001 2200 2007-05-11
6 10001 2500 2007-05-12
我想要的结果是：
通过组和日期(按周)来查询，
查询某周，某个组的所有员工，他下面的级别为A经销商，共有多少个，其中在线时长超过1000分钟的多少个，占总数的百分之几，注意在线时长是一直累加的，所以计算某周在线时长是这周天的分钟数减去这周一的分钟数
大致结果是这样的
用户选择某组 用户选择某周 查询
比如用户选择了北京组，选择了上周，出来结果如下
用户 A类数 a类中在线超过400分钟的经销商个数(这周天-这周一) 百分数
海淀 2 1 50%
谢谢了，高手帮忙,在线等
[解决办法]
为了方便高手，先做表先。。。
--创建表
create table tbl_WorkSubCity
(
Work_group char(2) ,
Work_group_Name nvarchar(10)
)
go
create table tbl_Work
(
Work_code char(4) ,
Work_group char(2) ,
Work_name nvarchar(10)
)
go
create table tbl_dealer
(
cDealer_Code char(5) ,
cDealer_Name nvarchar(10),
cSales_Code char(4),
clevel char(1)
)
go
create table tbl_dealer_onlinetime
(
sid int identity(1,1),
cDealer_Code char(5) ,
iminute int,
creg_date datetime
)
go
--插入数据
insert into tbl_WorkSubCity
select '01 ', '北京 ' union all
select '02 ', '上海 ' union all
select '03 ', '深圳 '
insert into tbl_Work
select '1000 ', '01 ', '海淀 ' union all
select '1001 ', '01 ', '朝阳 ' union all
select '1002 ', '02 ', '长宁 ' union all
select '1003 ', '02 ', '黄埔 ' union all
select '1004 ', '03 ', '福田 ' union all
select '1005 ', '03 ', '蛇口 '
insert into tbl_dealer
select '10000 ', '网易 ', '1000 ', 'a ' union all
select '10001 ', '新浪 ', '1000 ', 'a ' union all
select '10002 ', '华为 ', '1001 ', 'b ' union all
select '10003 ', '腾讯 ', '1001 ', 'b '
insert into tbl_dealer_onlinetime
select '10000 ', '1000 ', '2007-05-10 ' union all
select '10000 ', '1100 ', '2007-05-11 ' union all
select '10000 ', '1310 ', '2007-05-12 ' union all
select '10001 ', '2000 ', '2007-05-10 ' union all
select '10001 ', '2200 ', '2007-05-11 ' union all
select '10001 ', '2500 ', '2007-05-12 '
--＝。＝
[解决办法]
那你要知道周天是哪天,周1是哪天?
给你个oracle的写法,仅做参考,抛砖引玉!
//计算A类数
select t.Work_name ,count(*) as A类数
from (select * from tbl_Work tw,tbl_WorkSubCity twsc where tw.Work_group=twsc.Work_group and twsc.Work_group_Name= '北京 ')) t,
(select td.cDealer_Code,tdo.iminute from tbl_dealer td,(select cdealer_code,sum(decode(creg_date,周日,iminute,0))-sum(decode(creg_date,周一,iminute,0)) as iminute from tbl_dealer_onlinetime group by cdealer_code) tdo
where td.cDealer_Code=tdo.cdealer_code and td.clevel= 'a ') as a
where t.Work_code=td.Work_code
group by t.Work_name
//类中在线超过400分钟的经销商个数(这周天-这周一)
select t.Work_name ,count(*) as a类中在线超过400分钟的经销商个数
from (select * from tbl_Work tw,tbl_WorkSubCity twsc where tw.Work_group=twsc.Work_group and twsc.Work_group_Name= '北京 ')) t,
(select td.cDealer_Code,tdo.iminute from tbl_dealer td,(select cdealer_code,sum(decode(creg_date,周日,iminute,0))-sum(decode(creg_date,周一,iminute,0)) as iminute from tbl_dealer_onlinetime group by cdealer_code) tdo
where td.cDealer_Code=tdo.cdealer_code and td.clevel= 'a ') as a
where t.Work_code=td.Work_code
and a.iminute> =400
group by t.Work_name
暂时只会这样写,不知道怎么写a.iminute> =400放在select 中
看楼下的了!
[解决办法]
--这个兄弟真是搞脑子啊，表设计得太好了，范式！！！
declare @cWorkGroup char(2),@cDate1 char(10),@cDate2 char(10),@i int
--用户选择了北京组
select @cWorkGroup= '01 '
--用户选择了上周，因为没有周日，用周六 - 周五吧
select @cDate1= '2007-05-11 '
select @cDate2= '2007-05-12 '
--数据中没有〉=400的值，这里搞成300玩玩
select @i=290
select Work_name,iTotal,iNumber, cast(cast(1.00*iNumber / iTotal *100 as dec(5,2)) as char(5) )+ '% '
from
(
select Work_name,(select count(*) from tbl_dealer where clevel= 'a ' and cSales_Code in (select Work_code from tbl_Work where Work_group = @cWorkGroup)) as iTotal
,count(*) as iNumber
from (
select (select Work_group from tbl_Work where Work_code=g.cSales_Code) as Work_group
, (select Work_name from tbl_Work where Work_code=g.cSales_Code) as Work_name
,g.*
from
(select (select b.cSales_Code from tbl_dealer b where b.cDealer_Code = h.cdealer_code) as cSales_Code
, h.*
from (select cdealer_code , iminute
,(select iminute from tbl_dealer_onlinetime where cdealer_code = c.cdealer_code and creg_date=@cDate2 ) as iminute2
from tbl_dealer_onlinetime c
where cdealer_code in (select cDealer_Code
from tbl_dealer
where cSales_Code in (select Work_code from tbl_Work where Work_group = @cWorkGroup and clevel= 'a ' ))
and creg_date=@cDate1 ) h
where iminute2 > iminute + @i
) g)zz
group by Work_name,Work_group
) lastTemp
[解决办法]
drop table #b
select b.Work_name,c.A类数 ,d.cDealer_Code,d.iminute,d.creg_date,d.i_iminute into #b
from tbl_WorkSubCity a
inner join tbl_Work b on a.Work_group=b.Work_group
inner join (select *,(select count(1) from tbl_dealer where t.cSales_Code=cSales_Code and clevel= 'a ' ) A类数 from tbl_dealer t ) c on b.Work_code = c.cSales_Code
inner join (select *,iminute-(select iminute from tbl_dealer_onlinetime where creg_date = (select min(creg_date) from tbl_dealer_onlinetime where cdealer_code=t.cdealer_code) and cdealer_code=t.cdealer_code) i_iminute
from tbl_dealer_onlinetime t where DATEDIFF( ww ,creg_date, getdate())=1) d on c.cDealer_Code=d.cdealer_code
where a.Work_group_Name = '北京 '
select Work_name,A类数,cDealer_Code,count(1),count(1)*100/(select count(distinct cDealer_Code) from #b ) from #b where i_iminute> =400 group by Work_name,A类数,cDealer_Code
数据中没有〉=400的值，这里搞成300玩玩
有啊~有一个的啊
[解决办法]
drop table #b
select b.Work_name,c.A类数 ,d.cDealer_Code,d.iminute,d.creg_date,d.i_iminute into #b
from tbl_WorkSubCity a
inner join tbl_Work b on a.Work_group=b.Work_group
inner join (select *,(select count(1) from tbl_dealer where t.cSales_Code=cSales_Code and clevel= 'a ' ) A类数 from tbl_dealer t ) c on b.Work_code = c.cSales_Code
inner join (select *,iminute-(select iminute from tbl_dealer_onlinetime where creg_date = (select min(creg_date) from tbl_dealer_onlinetime where cdealer_code=t.cdealer_code) and cdealer_code=t.cdealer_code) i_iminute
from tbl_dealer_onlinetime t where DATEDIFF( ww ,creg_date, getdate())=1) d on c.cDealer_Code=d.cdealer_code
where a.Work_group_Name = '北京 '
select Work_name,A类数,cDealer_Code,(select count(distinct cDealer_Code) from #b where i_iminute> =200) [a类中在线超过400分钟的经销商个数(这周天-这周一)], (select count(distinct cDealer_Code) from #b where i_iminute> =200)*100/(select count(distinct cDealer_Code) from #b ) [百分数(%)]
from #b group by Work_name,A类数,cDealer_Code
改一改,应是这样,这个是200的,1(getdate())=1)为前一个星期,北京,要找的地方,a,为A类,
[解决办法]
select work_name,Alevel,Aminute,
case when totalcount> 0 then
Aminute*100/totalcount
else 0
end as bl
from
(
select work_name,
sum(
case clevel
when 'a ' then 1
else 0
end) as Alevel,
sum(
case when sminute> 1000 then 1 --條件一：在線時間超過總數1000
else 0
end) as Aminute,
count(1) totalcount
from tbl_work t
left join
(
select v.*,tt.sminute from tbl_dealer v
left join (select cdealer_code,sum(iminute) as sminute from tbl_dealer_onlinetime where cgeg_date> '2007-5-10 ' and cgeg_date < '2007-5-17 ' group by cdealer_code) tt
on v.cdealer_code=tt.cdealer_code
)vv on t.work_code=vv.csales_code
where work_group= '01 ' --條件三
group by work_name
)a
[解决办法]
create table tbl_WorkSubCity
(
Work_group char(2) ,
Work_group_Name nvarchar(10)
)
go
create table tbl_Work
(
Work_code char(4) ,
Work_group char(2) ,
Work_name nvarchar(10)
)
go
create table tbl_dealer
(
cDealer_Code char(5) ,
cDealer_Name nvarchar(10),
cSales_Code char(4),
clevel char(1)
)
go
create table tbl_dealer_onlinetime
(
sid int identity(1,1),
cDealer_Code char(5) ,
iminute int,
creg_date datetime
)
go
--插入数据
insert into tbl_WorkSubCity
select '01 ', '北京 ' union all
select '02 ', '上海 ' union all
select '03 ', '深圳 '
insert into tbl_Work
select '1000 ', '01 ', '海淀 ' union all
select '1001 ', '01 ', '朝阳 ' union all
select '1002 ', '02 ', '长宁 ' union all
select '1003 ', '02 ', '黄埔 ' union all
select '1004 ', '03 ', '福田 ' union all
select '1005 ', '03 ', '蛇口 '
insert into tbl_dealer
select '10000 ', '网易 ', '1000 ', 'a ' union all
select '10001 ', '新浪 ', '1000 ', 'a ' union all
select '10002 ', '华为 ', '1001 ', 'b ' union all
select '10003 ', '腾讯 ', '1001 ', 'b '
insert into tbl_dealer_onlinetime
select '10000 ',1000, '2007-05-10 ' union all
select '10000 ',1100, '2007-05-11 ' union all
select '10000 ',1310, '2007-05-12 ' union all
select '10001 ',2000, '2007-05-10 ' union all
select '10001 ',2200, '2007-05-11 ' union all
select '10001 ',2500, '2007-05-12 '
go
create proc p
@Work_group_Name varchar(100),
@time1 smalldatetime,
@time2 smalldatetime,
@ttime int=1000 --输入在线时间，默认为1000分钟
as
select 用户=(select work_name from tbl_work where Work_code =b.Work_code ),
A类数=count(1),
a类中符合要求个数=sum(case when d.onlinetime> =@ttime then 1 else 0 end),
百分数=cast(sum(case when d.onlinetime> =@ttime then 1 else 0 end)*100/count(1) as varchar)+ '% '
from
tbl_WorkSubCity a inner join tbl_Work b on a.Work_group=b.Work_group
inner join tbl_dealer c on b.Work_code=c.cSales_Code
left join
(select cDealer_Code,onlinetime=max(iminute)-min(iminute)
from tbl_dealer_onlinetime
where creg_date between @time1 and @time2 group by cDealer_Code) d
on c.cDealer_Code=d.cDealer_Code
where a.Work_group_Name=@Work_group_Name
and c.clevel= 'a '
group by b.Work_code
go
p '北京 ', '2007-05-10 ', '2007-05-17 ',400
drop table tbl_WorkSubCity,tbl_Work,tbl_dealer,tbl_dealer_onlinetime
drop proc p
[解决办法]
你可以调整我设置的那些变量的数值，执行一下看看结果啊
[解决办法]
select work_name,count(*) num,count(time) timenum,convert(decimal(18,2),count(time)*1.00/count(*)*1.00) ttt from (
select a.*,b.*,c.time from tbl_work a left join tbl_dealer b on a.work_code = b.csales_code
left join
(select cdealer_code,endmin-startmin time from
(select x.cDealer_code,x.iminute startmin,y.iminute endmin from tbl_dealer_onlinetime x left join tbl_dealer_onlinetime y
on x.cdealer_code = y.cdealer_code
and x.creg_date= '2007-05-10 00:00:00 ' and y.creg_date= '2007-05-12 00:00:00 ')
a where endmin is not null )
c on b.cdealer_code = c.cdealer_code and time> 400 ) xx
where work_group = '01 ' and clevel= 'a ' group by work_name
