34,576
社区成员
发帖
与我相关
我的任务
分享
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
*/
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
----------------------------------------------------------------
-- 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
*/
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 书架名
/*
书架名 书总数 书页数 目录总数 小节总数
书架A 2 300 4 9
书架B 2 700 4 0
*/
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.书架名
/*
书架名 书总数 书页数 目录总数 小节总数
书架A 2 300 4 9
书架B 2 700 4 NULL
*/
----------------------------------------------------------------
-- 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
*/
----------------------------------------------------------------
-- 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
*/
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.书架名
/*
书架名 书总数 书页数 目录总数 小节名称
书架A 9 1400 9 9
书架B 4 1400 4 0
*/
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.书架名