请教多个表查询统计的一个SQL语句

Question

select * from 
(select distinct 书架名  from 书架表A) a 
left join 
(
select  书架名,count(书ID) as 书总数  from 书架表A group by 书架名
)b on a.书架名 =b.书架名
left join (
select a.书架名,sum(b.书页数) as 总页数 from 书架表A a ,书表B b where a.书ID=b.书ID group by a.书架名
) c on a.书架名 =c.书架名
left join 
(
select a.书架名,count(目录ID) as 目录总数   from 目录表C a left join 书架表A b on a.书ID=b.书ID group by a.书架名
)d on a.书架名 =d.书架名
left join 
(
select b.书架名,count(小节名称) as 小节总数 from 小节表D a left join (
select a.书架名,count(目录ID) as 目录总数   from 目录表C a left join 书架表A b on a.书ID=b.书ID group by a.书架名
) b on a.目录ID =b.目录ID group by b.书架名
) e  on .书架名 =e.书架名

Answer 1

create table A(书架ID int,书架名  varchar(10),书ID int, 书名 varchar(10))
insert into A
select '1','书架A','1','NAME1'
union all select '1','书架A','2','NAME2'
union all select '2','书架B','3','NAME3'
union all select '2','书架B','4','NAME4'create table B(书ID  int,书页数 int)
insert into B
select 1,100
union all select 2,200
union all select 3,300
union all select 4,400create table C(书ID  int,目录ID int)
insert into C
select 1,1000
union all select 1,1001
union all select 2,1002
union all select 2,1003
union all select 3,1004
union all select 3,1005
union all select 4,1006
union all select 4,1007create table D(目录ID  int,小节名称 varchar(10))
insert into D
select 1000,'a'
union all select 1000,'b'
union all select 1001,'a'
union all select 1001,'b'
union all select 1002,'a'
union all select 1002,'b'
union all select 1003,'a'
union all select 1003,'b'
union all select 1003,'c'
go
select A.书架名,count(1) as 书总数,SUM(B.书页数) as 书页数,COUNT(C.目录ID) as 目录总数,count(D.小节名称) as 小节名称
from A
left join B on A.书ID=B.书ID
left join C on A.书ID=C.书ID
left join D on C.目录ID=D.目录ID
group by A.书架名/*
书架名 书总数 书页数 目录总数 小节名称
书架A 9 1400 9 9
书架B 4 1400 4 0
*/

Answer 2

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-12-17 15:49:58
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
-- Dec 28 2012 20:23:12 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([书架ID] int,[书架名] varchar(5),[书ID] int,[书名] varchar(5))
insert [A]
select 1,'书架A',1,'NAME1' union all
select 1,'书架A',2,'NAME2' union all
select 2,'书架B',3,'NAME3' union all
select 2,'书架B',4,'NAME4'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([书ID] int,[书页数] int)
insert [B]
select 1,100 union all
select 2,200 union all
select 3,300 union all
select 4,400
--> 测试数据：[C]
if object_id('[C]') is not null drop table [C]
go 
create table [C]([书ID] int,[目录ID] int)
insert [C]
select 1,1000 union all
select 1,1001 union all
select 2,1002 union all
select 2,1003 union all
select 3,1004 union all
select 3,1005 union all
select 4,1006 union all
select 4,1007
--> 测试数据：[D]
if object_id('[D]') is not null drop table [D]
go 
create table [D]([目录ID] int,[小节名称] varchar(1))
insert [D]
select 1000,'a' union all
select 1000,'b' union all
select 1001,'a' union all
select 1001,'b' union all
select 1002,'a' union all
select 1002,'b' union all
select 1003,'a' union all
select 1003,'b' union all
select 1003,'c'
--------------开始查询--------------------------
select A.[书架名],COUNT(DISTINCT a.书id)[书总数],SUM(DISTINCT b.[书页数])[书总页数] ,count(C.[目录ID])[总目录] ,COUNT(D.[小节名称])[总小节] 
from [A] LEFT JOIN b ON a.[书ID]=b.[书ID]
LEFT JOIN c ON b.[书ID]=c.[书ID]
LEFT JOIN D ON C.目录ID=D.目录ID
GROUP BY A.[书架名]
----------------结果----------------------------
/* 
书架名   书总数         书总页数        总目录         总小节
----- ----------- ----------- ----------- -----------
书架A   2           300         9           9
书架B   2           700         4           0
*/

Answer 3

修改一下----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-12-17 15:49:58
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
-- Dec 28 2012 20:23:12 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([书架ID] int,[书架名] varchar(5),[书ID] int,[书名] varchar(5))
insert [A]
select 1,'书架A',1,'NAME1' union all
select 1,'书架A',2,'NAME2' union all
select 2,'书架B',3,'NAME3' union all
select 2,'书架B',4,'NAME4'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([书ID] int,[书页数] int)
insert [B]
select 1,100 union all
select 2,200 union all
select 3,300 union all
select 4,400
--> 测试数据：[C]
if object_id('[C]') is not null drop table [C]
go 
create table [C]([书ID] int,[目录ID] int)
insert [C]
select 1,1000 union all
select 1,1001 union all
select 2,1002 union all
select 2,1003 union all
select 3,1004 union all
select 3,1005 union all
select 4,1006 union all
select 4,1007
--> 测试数据：[D]
if object_id('[D]') is not null drop table [D]
go 
create table [D]([目录ID] int,[小节名称] varchar(1))
insert [D]
select 1000,'a' union all
select 1000,'b' union all
select 1001,'a' union all
select 1001,'b' union all
select 1002,'a' union all
select 1002,'b' union all
select 1003,'a' union all
select 1003,'b' union all
select 1003,'c'
--------------开始查询--------------------------
select A.[书架名],COUNT( a.书id)[书总数],SUM(DISTINCT b.[书页数])[书总页数] ,count(C.[目录ID])[总目录] ,COUNT(D.[小节名称])[总小节] 
from [A] LEFT JOIN b ON a.[书ID]=b.[书ID]
LEFT JOIN c ON b.[书ID]=c.[书ID]
LEFT JOIN D ON C.目录ID=D.目录ID
GROUP BY A.[书架名]
----------------结果----------------------------
/* 
书架名   书总数         书总页数        总目录         总小节
----- ----------- ----------- ----------- -----------
书架A   9           300         9           9
书架B   4           700         4           0
*/

Answer 4

第一次统计有误，应该这样：create table A(书架ID int,书架名  varchar(10),书ID int, 书名 varchar(10))
insert into A
select '1','书架A','1','NAME1'
union all select '1','书架A','2','NAME2'
union all select '2','书架B','3','NAME3'
union all select '2','书架B','4','NAME4'create table B(书ID  int,书页数 int)
insert into B
select 1,100
union all select 2,200
union all select 3,300
union all select 4,400create table C(书ID  int,目录ID int)
insert into C
select 1,1000
union all select 1,1001
union all select 2,1002
union all select 2,1003
union all select 3,1004
union all select 3,1005
union all select 4,1006
union all select 4,1007create table D(目录ID  int,小节名称 varchar(10))
insert into D
select 1000,'a'
union all select 1000,'b'
union all select 1001,'a'
union all select 1001,'b'
union all select 1002,'a'
union all select 1002,'b'
union all select 1003,'a'
union all select 1003,'b'
union all select 1003,'c'
goselect t1.*,t2.目录总数,t3.小节总数
from (select A.书架名,count(1) as 书总数,sum(B.书页数) as 书页数
from A left join B on A.书ID=B.书ID
group by A.书架名)t1
left join (select A.书架名,COUNT(1) as 目录总数 from C left join A on C.书ID=A.书ID 
group by A.书架名)t2 on t1.书架名=t2.书架名
left join (select A.书架名,COUNT(1) as 小节总数 
from c 
inner join d on c.目录ID=d.目录ID 
inner join a on C.书ID=A.书ID
group by A.书架名)t3 on t1.书架名=t3.书架名/*
书架名 书总数 书页数 目录总数 小节总数
书架A 2 300 4 9
书架B 2 700 4 NULL
*/

Answer 5

从实际数据分析，书ID 只有4个id，应该是只有本书才对，楼主是这样吗？

Answer 6

----借用表：A、B、C、D
with t as (
select 书架名,count(1) 书总数  from A group by 书架名
)
select 书架名,书总数
,(select sum(b.书页数) FROM a join  b on a.书ID=b.书ID where a.书架名=t.书架名) 总页数
,(select count(1) from  a join  c on a.书ID=c.书ID where a.书架名=t.书架名) 目录总数
,(select count(1) from  a join  c on a.书ID=c.书ID join  d on c.目录ID=d.目录ID where a.书架名=t.书架名) 小节总数
from t 

Answer 7

实际情况中书的数量不一定啊。只是我上面的例子只写了4个书的ID.建表语句：create table A(书架ID int,书架名  varchar(10),书ID int, 书名 varchar(10))
insert into A
select '1','书架A','1','NAME1'
union all select '1','书架A','2','NAME2'
union all select '2','书架B','3','NAME3'
union all select '2','书架B','4','NAME4'create table B(书ID  int,书页数 int)
insert into B
select 1,100
union all select 2,200
union all select 3,300
union all select 4,400create table C(书ID  int,目录ID int)
insert into C
select 1,1000
union all select 1,1001
union all select 2,1002
union all select 2,1003
union all select 3,1004
union all select 3,1005
union all select 4,1006
union all select 4,1007create table D(目录ID  int,小节名称 varchar(10))
insert into D
select 1000,'a'
union all select 1000,'b'
union all select 1001,'a'
union all select 1001,'b'
union all select 1002,'a'
union all select 1002,'b'
union all select 1003,'a'
union all select 1003,'b'
union all select 1003,'c'
go
用子查询，就不会有重复计算的问题了：
select 书架名,SUM(书总数) as 书总数,SUM(书页数) as 书页数,
       SUM(目录总数) as 目录总数,
       SUM(小节总数) as 小节总数
from 
(
select a.书架名,
       (select count(*) from B where a.书ID = b.书ID )as 书总数,
       (select SUM(b.书页数) from b where  a.书ID = b.书ID) as 书页数,
       (select COUNT(*) from c where a.书ID = c.书ID) as 目录总数,
       (select count(*) from c inner join d on c.目录ID = d.目录ID 
        where a.书ID = c.书ID) as 小节总数
       
from A
)t
group by 书架名
/*
书架名 书总数 书页数 目录总数 小节总数
书架A 2 300 4 9
书架B 2 700 4 0
*/

Answer 8

目录总数应是两个4啊
/*
书架名    书总数    书页数    目录总数    小节总数
书架A     2          300            4        9
书架B     2          700            4        NULL
*/

Answer 9

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-12-17 15:49:58
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
-- Dec 28 2012 20:23:12 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([书架ID] int,[书架名] varchar(5),[书ID] int,[书名] varchar(5))
insert [A]
select 1,'书架A',1,'NAME1' union all
select 1,'书架A',2,'NAME2' union all
select 2,'书架B',3,'NAME3' union all
select 2,'书架B',4,'NAME4'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([书ID] int,[书页数] int)
insert [B]
select 1,100 union all
select 2,200 union all
select 3,300 union all
select 4,400
--> 测试数据：[C]
if object_id('[C]') is not null drop table [C]
go 
create table [C]([书ID] int,[目录ID] int)
insert [C]
select 1,1000 union all
select 1,1001 union all
select 2,1002 union all
select 2,1003 union all
select 3,1004 union all
select 3,1005 union all
select 4,1006 union all
select 4,1007
--> 测试数据：[D]
if object_id('[D]') is not null drop table [D]
go 
create table [D]([目录ID] int,[小节名称] varchar(1))
insert [D]
select 1000,'a' union all
select 1000,'b' union all
select 1001,'a' union all
select 1001,'b' union all
select 1002,'a' union all
select 1002,'b' union all
select 1003,'a' union all
select 1003,'b' union all
select 1003,'c'
--------------开始查询--------------------------
select A.[书架名],COUNT(DISTINCT a.书id)[书总数],SUM(DISTINCT b.[书页数])[书总页数] ,count(DISTINCT C.[目录ID])[总目录] ,COUNT(D.[小节名称])[总小节] 
from [A] LEFT JOIN b ON a.[书ID]=b.[书ID]
LEFT JOIN c ON b.[书ID]=c.[书ID]
LEFT JOIN D ON C.目录ID=D.目录ID
GROUP BY A.[书架名]
----------------结果----------------------------
/* 
书架名   书总数         书总页数        总目录         总小节
----- ----------- ----------- ----------- -----------
书架A   2           300         4           9
书架B   2           700         4           0
*/

Answer 10

我上面写的，应该也是对的把：create table A(书架ID int,书架名  varchar(10),书ID int, 书名 varchar(10))
insert into A
select '1','书架A','1','NAME1'
union all select '1','书架A','2','NAME2'
union all select '2','书架B','3','NAME3'
union all select '2','书架B','4','NAME4'create table B(书ID  int,书页数 int)
insert into B
select 1,100
union all select 2,200
union all select 3,300
union all select 4,400create table C(书ID  int,目录ID int)
insert into C
select 1,1000
union all select 1,1001
union all select 2,1002
union all select 2,1003
union all select 3,1004
union all select 3,1005
union all select 4,1006
union all select 4,1007create table D(目录ID  int,小节名称 varchar(10))
insert into D
select 1000,'a'
union all select 1000,'b'
union all select 1001,'a'
union all select 1001,'b'
union all select 1002,'a'
union all select 1002,'b'
union all select 1003,'a'
union all select 1003,'b'
union all select 1003,'c'
go

Answer 11

用子查询实现：select 书架名,SUM(书总数) as 书总数,SUM(书页数) as 书页数,
       SUM(目录总数) as 目录总数,
       SUM(小节总数) as 小节总数
from 
(
select a.书架名,
       (select count(*) from B where a.书ID = b.书ID )as 书总数,
       (select SUM(b.书页数) from b where  a.书ID = b.书ID) as 书页数,
       (select COUNT(*) from c where a.书ID = c.书ID) as 目录总数,
       (select count(*) from c inner join d on c.目录ID = d.目录ID 
        where a.书ID = c.书ID) as 小节总数
       
from A
)t
group by 书架名
/*
书架名 书总数 书页数 目录总数 小节总数
书架A 2 300 4 9
书架B 2 700 4 0
*/