--> liangCK小梁 于2008-10-13 --> 生成测试数据: #tb1 IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1 CREATE TABLE #tb1 (年级 INT,班级 INT) INSERT INTO #tb1 SELECT '1','101' UNION ALL SELECT '1','102' UNION ALL SELECT '2','201' UNION ALL SELECT '2','202' --> liangCK小梁 于2008-10-13 --> 生成测试数据: #tb2 IF OBJECT_ID('tempdb.dbo.#tb2') IS NOT NULL DROP TABLE #tb2 CREATE TABLE #tb2 (班级 INT,学生 VARCHAR(1)) INSERT INTO #tb2 SELECT '101','A' UNION ALL SELECT '101','B' UNION ALL SELECT '102','C' UNION ALL SELECT '102','D' UNION ALL SELECT '201','E' UNION ALL SELECT '201','F' UNION ALL SELECT '202','G' UNION ALL SELECT '202','H'--SQL查询如下: SELECT a.年级, COUNT(DISTINCT a.班级) 班级数, COUNT(*) 学生数 FROM #tb1 AS a JOIN #tb2 AS b ON a.班级=b.班级 GROUP BY a.年级/* 年级 班级数 学生数 ----------- ----------- ----------- 1 2 4 2 2 4(2 行受影响)*/
--> --> (Roy)生成測試數據
declare @T1 table([年级] int,[班级] int) Insert @T1 select 1,101 union all select 1,102 union all select 2,201 union all select 2,202
--> --> (Roy)生成測試數據
declare @T2 table([班级] int,[学生] nvarchar(1)) Insert @T2 select 101,N'A' union all select 101,N'B' union all select 102,N'C' union all select 102,N'D' union all select 201,N'E' union all select 201,N'F' union all select 202,N'G' union all select 202,N'H'
Select t1.[年级],count(*) 班数量,sum(con) 学生数 from @T1 t1 join (select [班级],count(*) con from @T2 group by [班级])t2 on t1.[班级]=t2.[班级] group by t1.[年级] (4 行受影响)(8 行受影响) 年级 班数量 学生数 ----------- ----------- ----------- 1 2 4 2 2 4(2 行受影响)
declare @表头A table ( 表头id int, 客户 char(1) )declare @表身B table ( 表身id int, 表头id int, 表身数量 int )declare @附属表C table ( 表身id int, 附属表数量 int )insert @表头A select 1,'a' union all select 2,'b'insert @表身B select 1,1,10 union all select 2,1,20 union all select 3,2,30 union all select 4,2,40insert @附属表C select 1,3 union all select 1,7 select a.客户, 表身数量 =( select sum(表身数量) from @表身B where 表头id = a.表头id ), 附属表数量 =isnull(( select sum(c.附属表数量) from @表身B b inner join @附属表C c on c.表身id = b.表身id where b.表头id = a.表头id ),0) from @表头A a/** a 30 10 b 70 0 **/
set nocount on declare @表头A table ( 表头id int, 客户 char(1) ) declare @表身B table ( 表身id int, 表头id int, 表身数量 int ) declare @附属表C table ( 表身id int, 附属表数量 int ) insert @表头A select 1,'a' union all select 2,'b' insert @表身B select 1,1,10 union all select 2,1,20 union all select 3,2,30 union all select 4,2,40 insert @附属表C select 1,3 union all select 1,7 from @表头A a select a.客户,b.表身数量,isnull(c.附属表数量,0)附属表数量 from @表头A a inner join (select 表头id ,sum(表身数量)表身数量 from @表身B group by 表头id)b on a.表头id=b.表头id left join(select 表身id ,sum(附属表数量)附属表数量 from @附属表C group by 表身id) c on a.表头id=c.表身id /* 客户 表身数量 附属表数量 ---- ----------- ----------- a 30 17 b 70 0 */
select GG.Grade as'年级',GN.cc as '班级数量' from #tbGrade GG join (select Grade,count(Class) cc from #tbGrade group by Grade) GN on GN.Grade=GG.Grade group by GG.Grade,GN.cc
CREATE TABLE #tbGrade (Grade INT,Class INT) INSERT INTO #tbGrade SELECT '1','101' UNION ALL SELECT '1','102' UNION ALL SELECT '2','201' UNION ALL SELECT '2','202'CREATE TABLE #tbClass (Class INT,Student VARCHAR(1)) INSERT INTO #tbClass SELECT '101','A' UNION ALL SELECT '101','B' UNION ALL SELECT '102','C' UNION ALL SELECT '102','D' UNION ALL SELECT '201','E' UNION ALL SELECT '201','F' UNION ALL SELECT '202','G' UNION ALL SELECT '202','H'SELECT a.Grade as '年级', COUNT(DISTINCT a.Class) '班级数', COUNT(*) '学生数' FROM #tbGrade AS a JOIN #tbClass AS b ON a.Class=b.Class GROUP BY a.Grade
create table 表头a ( 表头id int, 客户 nvarchar(10) ) create table 表身B ( 表身id int, 表头id int, 表身数量 nvarchar(10) )create table 附属表C ( 表身id int, --班级 附属表数量 nvarchar(10) )insert into 表头a values(1,'a') insert into 表头a values(2,'b') insert into 表身B values(1,1,'10') insert into 表身B values(2,1,'20') insert into 表身B values(3,2,'30') insert into 表身B values(4,2,'40') insert into 附属表C values(1,'3') insert into 附属表C values(1,'7') select xx.客户,zz.数量,zz.附属表数量 from ( select aa.表头 as 表头,aa.数量 as 数量,isnull(bb.ll,0) as 附属表数量 from ( select a.表头id as 表头,a.he as 数量 from (select 表头id,sum(cast( 表身数量 as int)) as he from 表身B group by 表头id) as a ) as aa left join (select ff.表头id as ff_id ,cc.ee as ll from(select sum(cast(附属表数量 as int)) as ee,表身id from 附属表C group by 表身id ) as cc,表身B as ff where ff.表身id = cc.表身id) as bb on aa.表头 = bb.ff_id )as zz,表头a xx where zz.表头 = xx.表头id
--> 生成测试数据: #tb1
IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1
CREATE TABLE #tb1 (年级 INT,班级 INT)
INSERT INTO #tb1
SELECT '1','101' UNION ALL
SELECT '1','102' UNION ALL
SELECT '2','201' UNION ALL
SELECT '2','202'
--> liangCK小梁 于2008-10-13
--> 生成测试数据: #tb2
IF OBJECT_ID('tempdb.dbo.#tb2') IS NOT NULL DROP TABLE #tb2
CREATE TABLE #tb2 (班级 INT,学生 VARCHAR(1))
INSERT INTO #tb2
SELECT '101','A' UNION ALL
SELECT '101','B' UNION ALL
SELECT '102','C' UNION ALL
SELECT '102','D' UNION ALL
SELECT '201','E' UNION ALL
SELECT '201','F' UNION ALL
SELECT '202','G' UNION ALL
SELECT '202','H'--SQL查询如下:
SELECT a.年级,
COUNT(DISTINCT a.班级) 班级数,
COUNT(*) 学生数
FROM #tb1 AS a
JOIN #tb2 AS b
ON a.班级=b.班级
GROUP BY a.年级/*
年级 班级数 学生数
----------- ----------- -----------
1 2 4
2 2 4(2 行受影响)*/
declare @T1 table([年级] int,[班级] int)
Insert @T1
select 1,101 union all
select 1,102 union all
select 2,201 union all
select 2,202
--> --> (Roy)生成測試數據
declare @T2 table([班级] int,[学生] nvarchar(1))
Insert @T2
select 101,N'A' union all
select 101,N'B' union all
select 102,N'C' union all
select 102,N'D' union all
select 201,N'E' union all
select 201,N'F' union all
select 202,N'G' union all
select 202,N'H'
Select
t1.[年级],count(*) 班数量,sum(con) 学生数
from
@T1 t1
join
(select [班级],count(*) con from @T2 group by [班级])t2 on t1.[班级]=t2.[班级]
group by t1.[年级]
(4 行受影响)(8 行受影响)
年级 班数量 学生数
----------- ----------- -----------
1 2 4
2 2 4(2 行受影响)
A和B一对多,B和C一对多
表头A:
表头id,客户
1 a
2 b表身B:
表身id,表头id,表身数量
1 1 10
2 1 20
3 2 30
4 2 40附属表C:
表身id,附属表数量
1 3
1 7我想要的结果:
客户,表身数量,附属表数量
a 30 10(3+7)
b 70 0
(
表头id int,
客户 char(1)
)declare @表身B table
(
表身id int,
表头id int,
表身数量 int
)declare @附属表C table
(
表身id int,
附属表数量 int
)insert @表头A select 1,'a'
union all select 2,'b'insert @表身B select 1,1,10
union all select 2,1,20
union all select 3,2,30
union all select 4,2,40insert @附属表C select 1,3
union all select 1,7
select
a.客户,
表身数量 =(
select sum(表身数量)
from @表身B
where 表头id = a.表头id
),
附属表数量 =isnull((
select sum(c.附属表数量)
from @表身B b
inner join @附属表C c
on c.表身id = b.表身id
where b.表头id = a.表头id
),0)
from @表头A a/**
a 30 10
b 70 0
**/
declare @表头A table
(
表头id int,
客户 char(1)
)
declare @表身B table
(
表身id int,
表头id int,
表身数量 int
)
declare @附属表C table
(
表身id int,
附属表数量 int
)
insert @表头A select 1,'a'
union all select 2,'b'
insert @表身B select 1,1,10
union all select 2,1,20
union all select 3,2,30
union all select 4,2,40
insert @附属表C select 1,3
union all select 1,7
from @表头A a
select a.客户,b.表身数量,isnull(c.附属表数量,0)附属表数量 from @表头A a inner join (select 表头id ,sum(表身数量)表身数量 from @表身B group by 表头id)b
on a.表头id=b.表头id
left join(select 表身id ,sum(附属表数量)附属表数量 from @附属表C group by 表身id) c on a.表头id=c.表身id
/*
客户 表身数量 附属表数量
---- ----------- -----------
a 30 17
b 70 0
*/
join (select Grade,count(Class) cc from #tbGrade group by Grade) GN on GN.Grade=GG.Grade group by GG.Grade,GN.cc
INSERT INTO #tbGrade
SELECT '1','101' UNION ALL
SELECT '1','102' UNION ALL
SELECT '2','201' UNION ALL
SELECT '2','202'CREATE TABLE #tbClass (Class INT,Student VARCHAR(1))
INSERT INTO #tbClass
SELECT '101','A' UNION ALL
SELECT '101','B' UNION ALL
SELECT '102','C' UNION ALL
SELECT '102','D' UNION ALL
SELECT '201','E' UNION ALL
SELECT '201','F' UNION ALL
SELECT '202','G' UNION ALL
SELECT '202','H'SELECT a.Grade as '年级',
COUNT(DISTINCT a.Class) '班级数',
COUNT(*) '学生数'
FROM #tbGrade AS a
JOIN #tbClass AS b
ON a.Class=b.Class
GROUP BY a.Grade
(
表头id int,
客户 nvarchar(10)
)
create table 表身B
(
表身id int,
表头id int,
表身数量 nvarchar(10)
)create table 附属表C
(
表身id int, --班级
附属表数量 nvarchar(10)
)insert into 表头a values(1,'a')
insert into 表头a values(2,'b')
insert into 表身B values(1,1,'10')
insert into 表身B values(2,1,'20')
insert into 表身B values(3,2,'30')
insert into 表身B values(4,2,'40')
insert into 附属表C values(1,'3')
insert into 附属表C values(1,'7') select xx.客户,zz.数量,zz.附属表数量 from ( select aa.表头 as 表头,aa.数量 as 数量,isnull(bb.ll,0) as 附属表数量 from ( select a.表头id as 表头,a.he as 数量 from (select 表头id,sum(cast( 表身数量 as int)) as he from 表身B group by 表头id) as a ) as aa left join
(select ff.表头id as ff_id ,cc.ee as ll from(select sum(cast(附属表数量 as int)) as ee,表身id from 附属表C group by 表身id ) as cc,表身B as ff where ff.表身id = cc.表身id) as bb on aa.表头 = bb.ff_id )as zz,表头a xx where zz.表头 = xx.表头id