******搞不定要失业了,快救我,急死了,分不够另开帖加,多表查询问题******

Question

对应关系，一个部门对应多个用户，通过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%谢谢了，高手帮忙,在线等

Answer 1

为了方便高手，先做表先
--创建表
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' --＝。＝

Answer 2

那你要知道周天是哪天,周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 中看楼下的了!

Answer 3

--这个兄弟真是搞脑子啊，表设计得太好了，范式！！！
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=290select 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

Answer 4

drop table #bselect 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玩玩
有啊~有一个的啊

Answer 5

结果:
（所影响的行数为 6 行）Work_name  A类数         cDealer_Code                         
---------- ----------- ------------ ----------- ----------- 
海淀         2           10001        1           50（所影响的行数为 1 行）

Answer 6

drop table #bselect 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类,

Answer 7

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

Answer 8

playwarcraft(时间就像乳沟,挤挤还是有的) ( ) 信誉：100    Blog   加为好友  2007-5-16 12:27:38  得分: 0  
 
 
   
偶喜歡LZ,把測試環境都寫好啦~~~
吃飯去啦...  
----------------
乳沟兄，你太帅了。学你一下。

Answer 9

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分钟
asselect 用户=(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_Codewhere a.Work_group_Name=@Work_group_Name 
and c.clevel='a'
group by b.Work_code 
go
p '北京','2007-05-10','2007-05-17',400drop table tbl_WorkSubCity,tbl_Work,tbl_dealer,tbl_dealer_onlinetime
drop proc p

Answer 10

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