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

2013-12-19

请教多个表查询统计的一个SQL语句现有几个书架，每个书架上放若干书，知道每本书的总页数，每本书的目录，每个

请教多个表查询统计的一个SQL语句
现有几个书架，每个书架上放若干书，知道每本书的总页数，每本书的目录，每个目录的小节
现在想统计每个书架上的书的总册数，总页数，目录总数，及小节总数

即有四个表

书架表A
书架ID 书架名     书ID  书名
1        书架A     1    NAME1
1        书架A     2    NAME2
2        书架B     3    NAME3
2        书架B     4    NAME4

书表B
书ID    书页数
1        100
2        200
3        300
4        400

目录表C
书ID    目录ID
1        1000
1        1001
2        1002
2        1003
3        1004
3        1005
4        1006
4        1007

小节表D
目录ID       小节名称
1000          a
1000          b
1001          a
1001          b
1002          a
1002          b
1003          a
1003          b
1003          c
........
1007          f

想形成下面这样的统计结果

书架名    书总数   总页数  目录总数   小节总数
书架A
书架B

请大家教我怎么写这个SQL语句啊？
[解决办法]

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,400

create 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,1007

create 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.书架名

/*
书架名书总数书页数目录总数小节名称
书架A9140099
书架B4140040
*/

[解决办法]
第一次统计有误，应该这样：

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,400

create 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,1007

create 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 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.书架名

/*
书架名书总数书页数目录总数小节总数
书架A230049
书架B27004NULL
*/

[解决办法]
----借用表：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
[解决办法]

引用:

Quote: 引用:

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

实际情况中书的数量不一定啊。只是我上面的例子只写了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,400

create 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,1007

create 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 书架名
/*
书架名书总数书页数目录总数小节总数
书架A230049
书架B270040
*/

[解决办法]

---------------------------------------------------------------- 
 
-- 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
*/

[解决办法]
用子查询实现：

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 书架名
/*
书架名书总数书页数目录总数小节总数
书架A230049
书架B270040
*/

热点排行