继续昨天SQL问题，请各位看看，用一条SQL实现

Question

本帖最后由 xxy802 于 2009-07-23 15:13:54 编辑

Answer 1

Select DepartMent, rname, ClassName,a. ClassName,sum(Click)
From DFZXUser  inner join
DFZXFile on DFZXFile. Name = DFZXUser. Name inner join
DFZXClass on DFZXClass. ClassID = DFZXFile. ClassID
Left join
(
Select ClassName, PClassID from DFZXClass
)a on a. PClassID = DFZXClass. ClassID 
Group by DepartMent, rname, ClassName 

Answer 2

和上次那个类似吧：declare @DFZXFile  table(ClassID varchar(20),filenames varchar(20),Click int,name varchar(20))insert into @DFZXFile
select '089378034246','阿苏大',6,'26zhong' union all
select '089378034246','打三防',7,'26zhong' union all
select '089378034246','司法所担负',8,'26zhong' union all
select '503357621066','三地风俗地方',4,'34zhong' union all
select '357609362851','司法',3,'34zhong' 
declare @DFZXUser table(name varchar(20),rname varchar(20),groupnumber varchar(20),
  DepartMent varchar(20))insert into  @DFZXUser 
select '26zhong','26中','181173858358','督学室' union all
select '34zhong ','34中','181173858358','督学室' union all
select '37zhong','37中','181173858358','督学室' union all
select '5zhong','5中','181173858358','基础教育科' union all
select '6zhong','6中','181173858358','基础教育科' union all
select '71zhong','71中','181173858358','职教科' union all
select '7zhong','7中','181234234235','行政计划科' declare @DFZXClass table(ClassID varchar(20),ClassName varchar(20),
  PClassID varchar(20))
 
insert into @DFZXClass
select '105376361575','小学教师教育中心',null union all
select '943993224422','中学教师教育中心',null union all
select '089378034246','学前教师教育中心','943993224422' union all
select '619128150758','培训部','943993224422' union all
select '602681298510','科研部',null union all
select '398167173246','信息中心','602681298510' union all
select '754980894961','校务办公室','398167173246' union all
select '503357621066','保卫科','754980894961' union all
select '764961852020','财务科',null union all
select '520051113077','固定资产科','764961852020' union all
select '357609362851','行政科','520051113077'declare @USERGroup table(groupnumber varchar(20),name varchar(20))insert into @USERGroup 
 select '181173858358','学校' union all
select '181234234235','教育局'
select  DepartMent as 部门,rname as 姓名,c.ClassName as [负责栏目（上级）],
    d.classname as [负责栏目（终极）],count(filenames) as 文章数,avg(click) as 平均点击数
from @DFZXFile a,@DFZXUser b,@DFZXClass c,@DFZXClass d
  ,@USERGroup e
where a.name=b.name and a.classid=d.classid and d.pclassid=c.classid
  and e.name='学校'
group by DepartMent,rname,d.ClassName,c.ClassName
部门                   姓名                   负责栏目（上级）             负责栏目（终极）             文章数         平均点击数
-------------------- -------------------- -------------------- -------------------- ----------- -----------
督学室                  26中                  中学教师教育中心             学前教师教育中心             3           7
督学室                  34中                  校务办公室                保卫科                  1           4
督学室                  34中                  固定资产科                行政科                  1           3(3 行受影响)

Answer 3

感谢 xuejie09242 我方在数据库执行 还是不对 不是'学校' 用户组的数据也出来了

Answer 4

用外连接select  b.DepartMent as 部门,rname as 姓名,c.ClassName as [负责栏目（上级）],
    d.classname as [负责栏目（终极）],count(filenames) as 文章数,avg(click) as 平均点击数
from @DFZXUser b left join @DFZXFile a on a.name=b.name
   left join @DFZXClass d on a.classid=d.classid 
   left join @DFZXClass c on d.pclassid=c.classid
   left join @USERGroup e on b.groupnumber=e.groupnumber
where e.name='学校'
group by DepartMent,rname,d.ClassName,c.ClassName

Answer 5

感谢 xuejie09242 你的SQL  groupnumber 没有关联，但关联起来 就没数据了

Answer 6

刚才漏掉了和用户组的关联条件了
刚才的查询结果是：
部门                   姓名                   负责栏目（上级）             负责栏目（终极）             文章数         平均点击数
-------------------- -------------------- -------------------- -------------------- ----------- -----------
督学室                  26中                  中学教师教育中心             学前教师教育中心             3           7
督学室                  34中                  校务办公室                保卫科                  1           4
督学室                  34中                  固定资产科                行政科                  1           3
督学室                  37中                  NULL                 NULL                 0           NULL
基础教育科                5中                   NULL                 NULL                 0           NULL
基础教育科                6中                   NULL                 NULL                 0           NULL
职教科                  71中                  NULL                 NULL                 0           NULL
警告: 聚合或其他 SET 操作消除了 Null 值。(7 行受影响)如果要平均值也显示为0，avg() 改为 isnull(avg(),0)如下：select  b.DepartMent as 部门,rname as 姓名,c.ClassName as [负责栏目（上级）],
    d.classname as [负责栏目（终极）],count(filenames) as 文章数,isnull(avg(click),0) as 平均点击数
from @DFZXUser b left join @DFZXFile a on a.name=b.name
   left join @DFZXClass d on a.classid=d.classid 
   left join @DFZXClass c on d.pclassid=c.classid
   left join @USERGroup e on b.groupnumber=e.groupnumber
where e.name='学校'
group by DepartMent,rname,d.ClassName,c.ClassName

Answer 7

-- 先上一个已传文件的部门,有编码过程declare @DFZXFile  table(ClassID char(12), filenames  char(22),Click int,name varchar(20))
declare @DFZXUser  table(name char(20), rname  char(22),groupnumber char(12),DepartMent varchar(20))
declare @DFZXClass table(ClassID char(12), ClassName char(40),PClassID varchar(12))
declare @USERGroup table(groupnumber char(12), name char(40))insert into @DFZXFile
select '089378034246','阿苏大',     6,'26zhong'  union all
select '089378034246','打三防',     7,'26zhong'  union all 
select '089378034246','司法所担负',  8,'26zhong'  union all 
select '503357621066','三地风俗地方', 4,'34zhong' union all 
select '357609362851','司法',        3,'34zhong' insert @DFZXUser 
select '26zhong','26中','181173858358','督学室 '      union all
select '34zhong','34中','181173858358','督学室 '      union all
select '37zhong','37中','181173858358','督学室 '      union all
select '5zhong', '5中 ','181173858358','基础教育科 '  union all
select '6zhong','6中  ','181173858358','基础教育科 '  union all
select '71zhong','71中','181173858358','职教科 '      union all
select '7zhong','7中  ','181234234235','行政计划科 'insert @DFZXClass 
select '105376361575',' 小学教师教育中心 ', ''      union all
select '943993224422',' 中学教师教育中心 ', ''        union all
select '089378034246',' 学前教师教育中心',  '943993224422'  union all
select '619128150758',' 培训部  ',         '943993224422'       union all
select '602681298510',' 科研部 ' ,    ''       union all
select '398167173246',' 信息中心 ',        '602681298510'      union all
select '754980894961',' 校务办公室 ',    '398167173246'      union all
select '503357621066',' 保卫科 ',          '754980894961 '      union all
select '764961852020',' 财务科 ' ,    ''             union all
select '520051113077 ','固定资产科',   '764961852020 '      union all
select '357609362851',' 行政科 ',   '520051113077 '  
insert @USERGroup 
select '181173858358','学校 'union all
select '181234234235','教育局 '-- ——————————————————————————————
--- 第一步，操作第一个表select ClassId,name,count(*) as 文章数,sum(click)/count(*) as 平均点击数 from @DFZXFile
group by ClassId,name
/*
ClassId      Name     文章数  平均点击数
089378034246 26zhong  3   7
357609362851 34zhong  1   3
503357621066 34zhong  1   4
*/
-----------------------------------------------------------
--- 第二步，连接表1，表3select [负责栏目(上级)]=c.ClassName,[负责栏目(终极)],b3.name,b3.文章数,b3.平均点击数
from (
select [负责栏目(终极)]=c.ClassName,PClassId,b1.* from @DFZXClass c
join (
select ClassId,name,count(*) as 文章数,[平均点击数]=(case when count(*)<>0 then sum(click)/count(*) else 0 end)  from @DFZXFile
group by ClassId,name
              )as b1
on c.ClassId=b1.ClassId
      ) as b3
join @DFZXClass c
on b3.PClassId=c.ClassId /*
负责栏目（上级）    负责栏目（终级）           name     文章数  平均点击数  中学教师教育中心   学前教师教育中心           26zhong  3 7  
校务办公室        保卫科                     34zhong 1 4
固定资产科        行政科                     34zhong 1 3
*/------------------------------------------------------------------
--- 第三步，连接步骤2的表和表2 select b.DepartMent, b.groupnumber,b.rname,b2.* from @DFZXUser b 
join(
select [负责栏目(上级)]=c.ClassName,[负责栏目(终极)],b3.name,b3.文章数,b3.平均点击数
from (
select [负责栏目(终极)]=c.ClassName,PClassId,b1.* from @DFZXClass c
join (
select ClassId,name,count(*) as 文章数,[平均点击数]=(case when count(*)<>0 then sum(click)/count(*) else 0 end)  from @DFZXFile
group by ClassId,name
                     )as b1
on c.ClassId=b1.ClassId
             ) as b3
join @DFZXClass c
on b3.PClassId=c.ClassId 
     ) as b2
on b.name=b2.name/*
DepartMent   groupnumber       rname      负责栏目（上级）     负责栏目（终级）           name     文章数  平均点击数 督学室       181173858358 26中       中学教师教育中心    学前教师教育中心           26zhong   3    7
督学室       181173858358 34中       固定资产科         行政科                     34zhong   1    3
督学室       181173858358 34中       校务办公室          保卫科                    34zhong   1    4
*/
-------------------------------------------------------------------------
--- 第四步，连接步骤3的表和表4中用户组为‘学校’的记录    （最终代码）
select departMent as 部门,rname as 姓名,[负责栏目(上级)],[负责栏目(终极)],文章数,平均点击数 from @USERGroup d
join(
select b.DepartMent, b.groupnumber,b.rname,b2.* from @DFZXUser b 
join(
select [负责栏目(上级)]=c.ClassName,[负责栏目(终极)],b3.name,b3.文章数,b3.平均点击数
from (
select [负责栏目(终极)]=c.ClassName,PClassId,b1.* from @DFZXClass c
join (
select ClassId,name,count(*) as 文章数,[平均点击数]=(case when count(*)<>0 then sum(click)/count(*) else 0 end)  from @DFZXFile
group by ClassId,name
                      )as b1
on c.ClassId=b1.ClassId
              ) as b3
join @DFZXClass c
on b3.PClassId=c.ClassId 
   ) as b2
on b.name=b2.name
   ) as b4
on b4.groupnumber=d.groupnumber 
where d.name='学校'/*部门      姓名      负责栏目（上级）     负责栏目（终级）           name     文章数  平均点击数 督学室    26中       中学教师教育中心    学前教师教育中心           26zhong   3    7
督学室    34中       固定资产科         行政科                     34zhong   1    3
督学室    34中       校务办公室          保卫科                    34zhong   1    4
*/

Answer 8

感谢js_szy
  我需要没有传文件的用户也要出来

Answer 9

declare @DFZXFile  table(ClassID char(12), filenames  char(22),Click int,name varchar(20))
declare @DFZXUser  table(name char(20), rname  char(22),groupnumber char(12),DepartMent varchar(20))
declare @DFZXClass table(ClassID char(12), ClassName char(40),PClassID varchar(12))
declare @USERGroup table(groupnumber char(12), name char(40))insert into @DFZXFile
select '089378034246','阿苏大',     6,'26zhong'  union all
select '089378034246','打三防',     7,'26zhong'  union all 
select '089378034246','司法所担负',  8,'26zhong'  union all 
select '503357621066','三地风俗地方', 4,'34zhong' union all 
select '357609362851','司法',        3,'34zhong' insert @DFZXUser 
select '26zhong','26中','181173858358','督学室 '      union all
select '34zhong','34中','181173858358','督学室 '      union all
select '37zhong','37中','181173858358','督学室 '      union all
select '5zhong', '5中 ','181173858358','基础教育科 '  union all
select '6zhong','6中  ','181173858358','基础教育科 '  union all
select '71zhong','71中','181173858358','职教科 '      union all
select '7zhong','7中  ','181234234235','行政计划科 'insert @DFZXClass 
select '105376361575',' 小学教师教育中心 ', ''                union all
select '943993224422',' 中学教师教育中心 ', ''              union all
select '089378034246',' 学前教师教育中心',  '943993224422'     union all
select '619128150758',' 培训部  ',         '943993224422'       union all
select '602681298510',' 科研部 ' ,       ''              union all
select '398167173246',' 信息中心 ',        '602681298510'      union all
select '754980894961',' 校务办公室 ',       '398167173246'      union all
select '503357621066',' 保卫科 ',          '754980894961 '      union all
select '764961852020',' 财务科 ' ,       ''                union all
select '520051113077 ','固定资产科',      '764961852020 '      union all
select '357609362851',' 行政科 ',      '520051113077 '  
insert @USERGroup 
select '181173858358','学校 'union all
select '181234234235','教育局 '
select departMent as 部门,rname as 姓名,isnull([负责栏目(上级)],'') as '[负责栏目(上级)]',
isnull([负责栏目(终极)],'') as '[负责栏目(终极)]',
isnull(文章数,0) as 文章数,isnull(平均点击数,0) as 平均点击数 from @USERGroup d
join(
    select b.DepartMent, b.groupnumber,b.rname,b2.* from @DFZXUser b left
    join(
        select [负责栏目(上级)]=c.ClassName,[负责栏目(终极)],b3.name,b3.文章数,b3.平均点击数
        from (
            select [负责栏目(终极)]=c.ClassName,PClassId,b1.* from @DFZXClass c left
            join (
                select ClassId,name,count(*) as 文章数,[平均点击数]=(case when count(*)<>0 then sum(click)/count(*) else 0 end)  from @DFZXFile
                group by ClassId,name
                         )as b1
            on c.ClassId=b1.ClassId
                 ) as b3 left
        join @DFZXClass c
        on b3.PClassId=c.ClassId 
       ) as b2
        on b.name=b2.name
   ) as b4
on b4.groupnumber=d.groupnumber 
where d.name='学校'/*部门                   姓名                     [负责栏目(上级)]                               [负责栏目(终极)]                               文章数         平均点击数
-------------------- ---------------------- ---------------------------------------- ---------------------------------------- ----------- -----------
督学室                  26中                     中学教师教育中心                                 学前教师教育中心                                3           7
督学室                  34中                     校务办公室                                    保卫科                                     1           4
督学室                  34中                    固定资产科                                     行政科                                     1           3
督学室                  37中                                                                                                      0           0
基础教育科                5中                                                                                                       0           0
基础教育科                6中                                                                                                       0           0
职教科                  71中                                                                                                      0           0(7 row(s) affected)
*/

Answer 10

问题找到了 关联的NAME写错了  谢谢各位

Answer 11

-- 下班了，没来得急，都结贴了啊select  e.部门,e.姓名,[负责栏目(上级)]=isnull([负责栏目(上级)],''),[负责栏目(终极)]=isnull([负责栏目(终极)],''),[文章数]=isnull(文章数,0),[平均点击数]=isnull(平均点击数,0) 
from(
select departMent as 部门,rname as 姓名 from @USERGroup d1
join @DFZXUser b1
on d1.groupnumber=b1.groupnumber
where d1.name='学校'
group by  departMent,rname
) as e
left join 
(
select departMent as 部门,rname as 姓名,[负责栏目(上级)],[负责栏目(终极)],文章数,平均点击数 from @USERGroup d
join(
select b.DepartMent, b.groupnumber,b.rname,b2.* from @DFZXUser b 
join(
select [负责栏目(上级)]=c.ClassName,[负责栏目(终极)],b3.name,b3.文章数,b3.平均点击数
from (
select [负责栏目(终极)]=c.ClassName,PClassId,b1.* from @DFZXClass c
join (
select ClassId,name,count(*) as 文章数,[平均点击数]=(case when count(*)<>0 then sum(click)/count(*) else 0 end)  from @DFZXFile
group by ClassId,name
                      )as b1
on c.ClassId=b1.ClassId
              ) as b3
join @DFZXClass c
on b3.PClassId=c.ClassId 
   ) as b2
on b.name=b2.name
   ) as b4
on b4.groupnumber=d.groupnumber 
where d.name='学校'
) as f
on e.部门=f.部门 and e.姓名=f.姓名/*
部门       姓名      负责栏目（上级）     负责栏目（终级）     文章数  平均点击数 
督学室     26中       中学教师教育中心    学前教师教育中心       3    7
督学室     34中        校务办公室         保卫科                1          4
督学室     34中        固定资产科         行政科                1          3
督学室     37中                                          0 0
基础教育科  5中                                           0 0
基础教育科  6中                                           0 0
职教科     71中                                          0 0
*/