请教一个SQL的写法，能否一条SQL实现，不用过程只用SQL

Question

请教一个SQL的写法，能否一条SQL实现，不用过程只用SQL
有三个表 结构数据如下，如何实现统计部分的查询结果？用户表机构  部委   姓名  年龄教育  办公室  张三   13
教育  办公室  我啊   22
教育  主任室  李斯   22
行政  保卫    萨阿   32
行政    保卫  哩斯   63------------------------------
结构表
主结构ID   栏目名   父结构ID111      啊啊 
334111   我啊啊     111
22234    三四三     334111
24378    啊啊       22234233      改革 
344   哈哈       233
455    地调动     344
456    鹅鹅鹅     455990     调动 
998   谔谔       990
887    辉煌     998
---------------------------------
资料表资料名   查看次数  资料位置  资料上传人
AAAAA    20        辉煌       张三
ASSSS    30        啊啊       张三
TTTTT    40        辉煌       张三
VVVVV    60        啊啊       张三
WWWWW    70        辉煌       张三
ASDFF    70        啊啊       我啊
DSAWR    70        辉煌       我啊
EWTYR    70        鹅鹅鹅       我啊
WERUI    70        鹅鹅鹅       我啊
OIYUT    60        啊啊       李斯
POIUY    70        辉煌       李斯
YUIOH    70        啊啊       李斯
JHGFD    70        辉煌       李斯
HGJKF    70        鹅鹅鹅       李斯
ERIOU    70        鹅鹅鹅       李斯
---------------------------
要实现下面查询结果
---------------------------
教育机构下文件数量统计部委 姓名 结构（终极的上一级） 结构（终极） 资料数 平均查看数办公室 张三 谔谔                         辉煌             3       43
办公室 张三 三四三                      啊啊          2       45
办公室 我啊 地调动               鹅鹅鹅           2       70
办公室 我啊 三四三               啊啊             1       70
办公室 我啊 谔谔                 辉煌          1       70
主任室 李斯 地调动               鹅鹅鹅           2       70
主任室 李斯 三四三               啊啊             2       70
主任室 李斯 谔谔                 辉煌             2       70

Answer 1

同意樓上說法，無法一個sql語法組成，可利用tmp table的方式先處理前面的資料，而後面那個平均查看數可能無法直接由一段sql語法組成。

Answer 2

本帖最后由 roy_88 于 2009-07-22 17:00:54 编辑

  

Answer 3

create table 用户表 (机构 varchar(20),  部委  varchar(20),  姓名 varchar(20),  年龄  int)
insert into 用户表
select '教育'  ,'办公室' , '张三' , 13  union all
select '教育'  ,'办公室' , '我啊',  22  union all
select '教育'  ,'主任室' , '李斯' , 22  union all
select '行政'  ,'保卫'   , '萨阿' , 32  union all
select '行政'  ,  '保卫' , '哩斯' , 63 create table 结构表 (主结构ID varchar(20),  栏目名 varchar(20), 父结构ID varchar(20))
insert into 结构表
select '111'    ,  '啊啊 ',''  union all
select '334111' , '我啊啊'  ,  '111'   union all
select '22234'  ,  '三四三 ' ,  '334111'   union all
select '24378'  ,  '啊啊'   ,   '22234'   union allselect '233'   ,   '改革', ''  union all
select '344'  ,'哈哈'   ,   '233'   union all
select '455'  ,  '地调动' ,   '344 '  union all
select '456'  ,  '鹅鹅鹅'  ,  '455 '  union allselect '990'  ,  '调动', ''  union all
select '998'  ,'谔谔'   ,   '990'   union all
select '887'  ,  '辉煌' ,   '998'   create table 资料表 (资料名 varchar(20), 查看次数 int,  资料位置  varchar(20),资料上传人 varchar(20))
insert into 资料表
select 'AAAAA'  ,  20   ,     '辉煌'  ,    '张三'  union all
select 'ASSSS'  ,  30   ,     '啊啊'  ,    '张三' union all
select 'TTTTT'  ,  40  ,      '辉煌'   ,   '张三' union all
select 'VVVVV' ,   60   ,     '啊啊'   ,   '张三' union all
select 'WWWWW'  ,  70   ,     '辉煌'   ,   '张三' union all
select 'ASDFF'  ,  70   ,     '啊啊'   ,   '我啊' union all
select 'DSAWR'  ,  70    ,    '辉煌'   ,   '我啊' union all
select 'EWTYR'  ,  70    ,    '鹅鹅鹅' ,     '我啊' union all
select 'WERUI' ,   70   ,     '鹅鹅鹅' ,     '我啊' union all
select 'OIYUT' ,   60    ,    '啊啊'  ,    '李斯' union all
select 'POIUY' ,   70    ,    '辉煌'  ,    '李斯' union all
select 'YUIOH' ,   70    ,    '啊啊'  ,    '李斯' union all
select 'JHGFD'  ,  70   ,     '辉煌'  ,    '李斯' union all
select 'HGJKF' ,   70    ,    '鹅鹅鹅' ,     '李斯' union all
select 'ERIOU'  ,  70     ,   '鹅鹅鹅'  ,    '李斯'select *from 用户表
select * from 结构表
select * from 资料表  
select 用户表.部委 ,A.* from 
(select 资料上传人,资料位置, count(资料位置) as cnt ,avg(查看次数)  as avgcnt
from 资料表  group by 资料位置,资料上传人 )  as A
left join 
用户表
on A.资料上传人 = 用户表.姓名
order by A.avgcnt
========================办公室 张三 辉煌 3 43
办公室 张三 啊啊 2 45
主任室 李斯 啊啊 2 65
主任室 李斯 鹅鹅鹅 2 70
主任室 李斯 辉煌 2 70
办公室 我啊 啊啊 1 70
办公室 我啊 鹅鹅鹅 2 70
办公室 我啊 辉煌 1 70
-----能里有限，就到这了！

Answer 4

感谢 roy_88 你写的查不多了
不过有点小问题可能是我没写明白
/*
部委    姓名    上級    頂級    资料数    查看次数
办公室    张三    NULL    啊啊    2    45
办公室    张三    谔谔    调动    3    43
办公室    我啊    NULL    啊啊    1    70
办公室    我啊    谔谔    调动    1    70
办公室    我啊    地调动    改革    2    70
主任室    李斯    NULL    啊啊    2    65
主任室    李斯    谔谔    调动    2    70
主任室    李斯    地调动    改革    2    70*/这里面的 頂級 应该是  文件 所在的结构 也就是 
24378    啊啊      22234 
456    鹅鹅鹅    455 
87    辉煌    998  这三个
上級  应该是
22234    三四三    334111 
455    地调动    344 
98  谔谔      990  这三个 

Answer 5

select a.部委,a.姓名,d.栏目名 as 上一级结构,c.栏目名 as 结构
,count(b.资料名) as 资料数,avg(b.查看次数) as 平均查看数
from #用户表 a
join #资料表 b on a.姓名=b.资料上传人
join #结构表 c on b.资料位置=c.栏目名
join #结构表 d on c.父结构ID=d.主结构ID
group by a.部委,a.姓名,d.栏目名,c.栏目名
order by a.部委,a.姓名 desc
/*
部委   姓名   上一级结构 结构   资料数         平均查看数
---- ---- ----- ---- ----------- -----------
办公室  张三   谔谔    辉煌   3           43
办公室  张三   三四三   啊啊   2           45
办公室  我啊   地调动   鹅鹅鹅  2           70
办公室  我啊   谔谔    辉煌   1           70
办公室  我啊   三四三   啊啊   1           70
主任室  李斯   地调动   鹅鹅鹅  2           70
主任室  李斯   谔谔    辉煌   2           70
主任室  李斯   三四三   啊啊   2           65(8 行受影响)
*/

Answer 6

感谢szx1999  我执行你的SQL 结果是
办公室 张三 谔谔 辉煌 3 43
办公室 我啊 地调动 鹅鹅鹅 2 70
办公室 我啊 谔谔 辉煌 1 70
主任室 李斯 地调动 鹅鹅鹅 2 70
主任室 李斯 谔谔 辉煌 2 70

Answer 7

你给的数据可能有些出入。
我的建表语句如下，你可以核对一下。逻辑上应该没问题。
if not object_id(N'Tempdb..#用户表') is null
    drop table #用户表
Go
Create table #用户表([机构] nvarchar(2),[部委] nvarchar(3),[姓名] nvarchar(2),[年龄] int)
Insert #用户表
select N'教育',N'办公室',N'张三',13 union all
select N'教育',N'办公室',N'我啊',22 union all
select N'教育',N'主任室',N'李斯',22 union all
select N'行政',N'保卫',N'萨阿',32 union all
select N'行政',N'保卫',N'哩斯',63
Go
 
if not object_id(N'Tempdb..#结构表') is null
    drop table #结构表
Go
Create table #结构表([主结构ID] int,[栏目名] nvarchar(3),[父结构ID] int)
Insert #结构表
select 111,N'啊啊啊',null union all
select 334111,N'我啊啊',111 union all
select 22234,N'三四三',334111 union all
select 24378,N'啊啊',22234 union all--這條記錄重復+多字啊
select 233,N'改革',null union all
select 344,N'哈哈',233 union all
select 455,N'地调动',344 union all
select 456,N'鹅鹅鹅',455 union all
select 990,N'调动',null union all
select 998,N'谔谔',990 union all
select 887,N'辉煌',998
Goif not object_id(N'Tempdb..#资料表') is null
    drop table #资料表
Go
Create table #资料表([资料名] nvarchar(5),[查看次数] int,[资料位置] nvarchar(3),[资料上传人] nvarchar(2))
Insert #资料表
select N'AAAAA',20,N'辉煌',N'张三' union all
select N'ASSSS',30,N'啊啊',N'张三' union all
select N'TTTTT',40,N'辉煌',N'张三' union all
select N'VVVVV',60,N'啊啊',N'张三' union all
select N'WWWWW',70,N'辉煌',N'张三' union all
select N'ASDFF',70,N'啊啊',N'我啊' union all
select N'DSAWR',70,N'辉煌',N'我啊' union all
select N'EWTYR',70,N'鹅鹅鹅',N'我啊' union all
select N'WERUI',70,N'鹅鹅鹅',N'我啊' union all
select N'OIYUT',60,N'啊啊',N'李斯' union all
select N'POIUY',70,N'辉煌',N'李斯' union all
select N'YUIOH',70,N'啊啊',N'李斯' union all
select N'JHGFD',70,N'辉煌',N'李斯' union all
select N'HGJKF',70,N'鹅鹅鹅',N'李斯' union all
select N'ERIOU',70,N'鹅鹅鹅',N'李斯'
Goselect *from #用户表
select * from #结构表
select * from #资料表 

Answer 8

szx1999的　没问题，看一下你的要求：
部委 姓名 结构（终极的上一级） 结构（终极） 资料数 平均查看数 部委，姓名来自于人员表，不用多说
结构（终极）来自资料表，资料数和平均查看数只要在资料表中按人员和资料进行　分组统计下就可以了，一个是count，另一个是avg看一下结构终极　你的要求是求父级结构名称，而不是其顶级结构，即没有父结点的结构，这在资料表中是有字段对应的，因此，只要个子查询就够了

Answer 9

这不就是很简单的join? 根本用不到recursive cte。
select tmp.[部委], tmp.[姓名], d.[栏目名] as [结构(终极的上一级)], tmp.[资料位置] as [结构(终极)], tmp.资料数, tmp.平均查看数
from
(select [部委], [姓名], [资料位置] , COUNT([资料上传人]) as [资料数], AVG([查看次数]) as [平均查看数]
from 
#用户表 a join #资料表 c
on a.[姓名] = c.[资料上传人]
group by [部委], [姓名], [资料位置]) tmp 
join 
#结构表 b 
left join #结构表 d
on b.[父结构ID] = d.[主结构ID]
on tmp.[资料位置] = b.[栏目名]
order by [部委],[姓名]/*
部委 姓名 结构(终极的上一级) 结构(终极) 资料数 平均查看数
主任室 李斯 三四三 啊啊 2 65
主任室 李斯 谔谔 辉煌 2 70
主任室 李斯 地调动 鹅鹅鹅 2 70
办公室 张三 三四三 啊啊 2 45
办公室 张三 谔谔 辉煌 3 43
办公室 我啊 三四三 啊啊 1 70
办公室 我啊 谔谔 辉煌 1 70
办公室 我啊 地调动 鹅鹅鹅 2 70
*/

Answer 10

还有一个条件 就是 机构 = 教育下面的所有用户，这条都没有添加 可能是我写的不清楚，我添加后就出错[b][/b][size=16px][/size]

Answer 11

看了一下CTE：
公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。CTE 可用于：
创建递归查询。有关详细信息，请参阅使用公用表表达式的递归查询。
在不需要常规使用视图时替换视图，也就是说，不必将定义存储在元数据中。
启用按从标量嵌套 select 语句派生的列进行分组，或者按不确定性函数或有外部访问的函数进行分组。
在同一语句中多次引用生成的表。使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。CTE 由表示 CTE 的表达式名称、可选列列表和定义 CTE 的查询组成。定义 CTE 后，可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中对其进行引用，就像引用表或视图一样。CTE 也可用于 CREATE VIEW 语句，作为定义 SELECT 语句的一部分。

Answer 12

select a.部委,a.姓名,d.栏目名 as 上一级结构,c.栏目名 as 结构
        ,count(b.资料名) as 资料数,avg(b.查看次数) as 平均查看数
from #用户表 a
join #资料表 b on a.姓名=b.资料上传人
join #结构表 c on b.资料位置=c.栏目名
join #结构表 d on c.父结构ID=d.主结构ID
where a.机构='教育'                   ----------这里加个条件即可
group by a.部委,a.姓名,d.栏目名,c.栏目名
order by a.部委,a.姓名 desc
/*
部委   姓名   上一级结构 结构   资料数         平均查看数
---- ---- ----- ---- ----------- -----------
办公室  张三   谔谔    辉煌   3           43
办公室  张三   三四三   啊啊   2           45
办公室  我啊   地调动   鹅鹅鹅  2           70
办公室  我啊   谔谔    辉煌   1           70
办公室  我啊   三四三   啊啊   1           70
主任室  李斯   地调动   鹅鹅鹅  2           70
主任室  李斯   谔谔    辉煌   2           70
主任室  李斯   三四三   啊啊   2           65(8 行受影响)
*/

Answer 13

实现我说的那个吧，应该只能用CTE了... ...
建表语句用的10楼的。10楼的CTE定义部分改一下：with Cte
as
(select *,[頂級]=[栏目名],上級=cast(栏目名 as nvarchar(400)) from #结构表 where [父结构ID] is null
union all
select a.*,a.[栏目名],cast(b.[栏目名] as nvarchar(400)) from #结构表 a inner join Cte b on a.[父结构ID]=b.[主结构ID]
)
蓉儿的最后查询部分再改一下：select a.部委,a.姓名,d.上級 as 根结构,c.栏目名 as 结构
        ,count(b.资料名) as 资料数,avg(b.查看次数) as 平均查看数
from #用户表 a
join #资料表 b on a.姓名=b.资料上传人
join #结构表 c on b.资料位置=c.栏目名
join Cte d on c.栏目名=d.頂級
group by a.部委,a.姓名,d.頂級,c.栏目名,d.上級
order by a.部委,a.姓名 desc

Answer 14

with Cte  可否用 
select a.结构, isnull(b.栏目名+'/','')+a.栏目名as 栏目名 from 结构表 a left join 结构表 b  on a.父结构ID =b.主结构ID
代替 ？   

Answer 15

如果只是查上一级结构，而不是根结构的话，不需要CTE。
select a.部委,a.姓名,d.栏目名 as 上一级结构,c.栏目名 as 结构
        ,count(b.资料名) as 资料数,avg(b.查看次数) as 平均查看数
from #用户表 a
join #资料表 b on a.姓名=b.资料上传人
join #结构表 c on b.资料位置=c.栏目名
left join #结构表 d on c.父结构ID=d.主结构ID  --这里改成left join.
where a.机构='教育'                   
group by a.部委,a.姓名,d.栏目名,c.栏目名
order by a.部委,a.姓名 desc

Answer 16

select a.部委,a.姓名,d.栏目名 as 上一级结构,c.栏目名 as 结构
        ,count(b.资料名) as 资料数,avg(b.查看次数) as 平均查看数
from #用户表 a
join #资料表 b on a.姓名=b.资料上传人
join #结构表 c on b.资料位置=c.栏目名
join #结构表 d on c.父结构ID=d.主结构ID
where a.机构='教育'                   ----------这里加个条件即可
group by a.部委,a.姓名,d.栏目名,c.栏目名
order by a.部委,a.姓名 desc

Answer 17

这个在sql server里要用CTE实现，在oracle里用一个connect by就可轻易实现

Answer 18

太乱了。就算一名sql能解决，也不要用。

Answer 19

看看这个能不能满足你的要求，
本人没有经过测试，也许会出现编译的小毛病。
但是基本思路想就是这样SELECT 
    (SELECT 
        部委
    FROM
        姓名 = T1.姓名
    WHERE 
        ROWNUM < 2
    ) 部委,
   T1.姓名,
   (SELECT 
       栏目名
    FROM
       结构表
    WHERE
       主结构ID = T1.P_FOLDER
   ) 终极的上一级
   T1.资料位置 终极, 
   T1.资料数,
   T1.平均查看数
FROM
   (SELECT 
       AVGR(查看次数) 平均查看数,
       COUNT(*), 资料数
       姓名,
       资料位置,
       (SELECT 
            父结构ID
        FROM
            结构表
        WHERE
            栏目名 = 资料位置
        ) P_FOLDER
   FROM
       资料表
   GROUP BY
       资料上传人,资料位置
   ) T1