--try: select school_name,grade_count=count(b.school_id),class_count=count(c.id) from tbSchool a left join tbGrade b on a.id=b.school_id left join tbClass c on c.grade_id=b.id group by a.school_name order by a.school_name
select a.school_name,isnull(count(distinct b.grade_id),0) as grade_count,isnull(count(c.classid),0) as class_count from tbSchool a left outer join (select x.school_id,y.grade_id,y.id as class_id from tbGrade x join tbClass y on x.id=y.grade_id) b on a.id=b.school_id
select school_name,grade_count=count(distinct b.school_id),class_count=count(c.id) from tbSchool a left join tbGrade b on a.id=b.school_id left join tbClass c on c.grade_id=b.id group by a.school_name order by a.school_name
--modify select school_name,grade_count=count(distinct b.school_id),class_count=count(c.id) from tbSchool a left join tbGrade b on a.id=b.school_id left join tbClass c on c.grade_id=b.id group by a.school_name order by a.school_name
SELSECT T.school_name, ISNULL(T.grade_count.0)grade_count, ISNULL(T1.class_count.0)class_count FROM (SELECT school_name , school_id , COUNT(school_id )grade_count FROM tbSchool JOIN tbGrade ON tbSchool.id=tbGrade.school_id GROUP BY school_name,school_id )AS T JOIN (SELECT school_id , COUNT(grade_id )class_count FROM tbClass JOIN tbGrade ON tbClass . grade_id =tbGrade.school_id GROUP BY school_id,grade_id)AS T1 ON T.school_name=T1.school_name
tbClass.grade_id->tbGrade.id
tbGrade.school_id->tbSchool.id
select school_name,grade_count=count(b.school_id),class_count=count(c.id) from tbSchool a
left join tbGrade b on a.id=b.school_id
left join tbClass c on c.grade_id=b.id
group by a.school_name
order by a.school_name
from tbSchool a
left outer join (select x.school_id,y.grade_id,y.id as class_id
from tbGrade x
join tbClass y on x.id=y.grade_id) b
on a.id=b.school_id
left join tbGrade b on a.id=b.school_id
left join tbClass c on c.grade_id=b.id
group by a.school_name
order by a.school_name
select school_name,grade_count=count(distinct b.school_id),class_count=count(c.id) from tbSchool a
left join tbGrade b on a.id=b.school_id
left join tbClass c on c.grade_id=b.id
group by a.school_name
order by a.school_name
insert @tbSchool select 2, '二中'
insert @tbSchool select 3 , '三中' declare @tbGrade table(id int, school_id int, grade_name varchar(10)) insert @tbGrade select 1 , 1 , '初一'
insert @tbGrade select 2 , 1 , '初二'
insert @tbGrade select 3 , 1 , '初三'
insert @tbGrade select 4 , 1 , '高一'
insert @tbGrade select 5 , 1 , '高二'
insert @tbGrade select 6 , 1 , '高三'
insert @tbGrade select 7 , 2 , '初一'
insert @tbGrade select 8 , 2 , '初二'
insert @tbGrade select 9 , 2 , '初三'
insert @tbGrade select 10 , 2 , '高一'
insert @tbGrade select 11, 2 , '高二'
insert @tbGrade select 12 , 2 , '高三' declare @tbClass table(id int, grade_id int, class_name varchar(10))insert @tbClass select 1 , 1 , '一班'
insert @tbClass select 2 , 1 , '二班'
insert @tbClass select 3 , 1 , '三班'
insert @tbClass select 4 , 1 , '四班'
insert @tbClass select 5 , 2 , '一班'
insert @tbClass select 6 , 2 , '二班'
insert @tbClass select 7 , 2 , '三班'
insert @tbClass select 8 , 2 , '四班'
insert @tbClass select 9 , 3 , '一班'
insert @tbClass select 10, 3 , '二班'
insert @tbClass select 11 , 3 , '三班'
insert @tbClass select 12 , 3 , '四班' -- 统计出以下数据集:
--
-- school_name grade_count class_count
-- 一中 6 12
-- 二中 6 0
-- 三中 0 0
--modify
select school_name,grade_count=count(distinct b.id),class_count=count(c.id)
from @tbSchool a
left join @tbGrade b on a.id=b.school_id
left join @tbClass c on c.grade_id=b.id
group by a.school_name
--order by a.school_name
/*
school_name grade_count class_count
----------- ----------- -----------
二中 6 0
三中 0 0
一中 6 12(所影响的行数为 3 行)
*/
insert @tbSchool select 1, '一中'
insert @tbSchool select 2, '二中'
insert @tbSchool select 3 , '三中'
declare @tbGrade table(id int, school_id int, grade_name varchar(10))
insert @tbGrade select 1 , 1 , '初一'
insert @tbGrade select 2 , 1 , '初二'
insert @tbGrade select 3 , 1 , '初三'
insert @tbGrade select 4 , 1 , '高一'
insert @tbGrade select 5 , 1 , '高二'
insert @tbGrade select 6 , 1 , '高三'
insert @tbGrade select 7 , 2 , '初一'
insert @tbGrade select 8 , 2 , '初二'
insert @tbGrade select 9 , 2 , '初三'
insert @tbGrade select 10 , 2 , '高一'
insert @tbGrade select 11, 2 , '高二'
insert @tbGrade select 12 , 2 , '高三'
declare @tbClass table(id int, grade_id int, class_name varchar(10))
insert @tbClass select 1 , 1 , '一班'
insert @tbClass select 2 , 1 , '二班'
insert @tbClass select 3 , 1 , '三班'
insert @tbClass select 4 , 1 , '四班'
insert @tbClass select 5 , 2 , '一班'
insert @tbClass select 6 , 2 , '二班'
insert @tbClass select 7 , 2 , '三班'
insert @tbClass select 8 , 2 , '四班'
insert @tbClass select 9 , 3 , '一班'
insert @tbClass select 10, 3 , '二班'
insert @tbClass select 11 , 3 , '三班'
insert @tbClass select 12 , 3 , '四班' -- 统计出以下数据集:
--
-- school_name grade_count class_count
-- 一中 6 12
-- 二中 6 0
-- 三中 0 0
--modify
select school_name,grade_count=count(distinct b.id),class_count=count(c.id)
from @tbSchool a
left join @tbGrade b on a.id=b.school_id
left join @tbClass c on c.grade_id=b.id
group by a.school_name
order by grade_count desc,class_count desc
/*
school_name grade_count class_count
----------- ----------- -----------
一中 6 12
二中 6 0
三中 0 0(所影响的行数为 3 行)
*/
insert @tbSchool select 2, '二中'
insert @tbSchool select 3 , '三中' declare @tbGrade table(id int, school_id int, grade_name varchar(10)) insert @tbGrade select 1 , 1 , '初一'
insert @tbGrade select 2 , 1 , '初二'
insert @tbGrade select 3 , 1 , '初三'
insert @tbGrade select 4 , 1 , '高一'
insert @tbGrade select 5 , 1 , '高二'
insert @tbGrade select 6 , 1 , '高三'
insert @tbGrade select 7 , 2 , '初一'
insert @tbGrade select 8 , 2 , '初二'
insert @tbGrade select 9 , 2 , '初三'
insert @tbGrade select 10 , 2 , '高一'
insert @tbGrade select 11, 2 , '高二'
insert @tbGrade select 12 , 2 , '高三' declare @tbClass table(id int, grade_id int, class_name varchar(10))insert @tbClass select 1 , 1 , '一班'
insert @tbClass select 2 , 1 , '二班'
insert @tbClass select 3 , 1 , '三班'
insert @tbClass select 4 , 1 , '四班'
insert @tbClass select 5 , 2 , '一班'
insert @tbClass select 6 , 2 , '二班'
insert @tbClass select 7 , 2 , '三班'
insert @tbClass select 8 , 2 , '四班'
insert @tbClass select 9 , 3 , '一班'
insert @tbClass select 10, 3 , '二班'
insert @tbClass select 11 , 3 , '三班'
insert @tbClass select 12 , 3 , '四班' select school_name,grade_count=count(distinct b.id),class_count=count(c.id)
from @tbSchool a
left join @tbGrade b on a.id=b.school_id
left join @tbClass c on c.grade_id=b.id
group by a.school_name
order by grade_count desc,class_count desc
/*
school_name grade_count class_count
----------- ----------- -----------
一中 6 12
二中 6 0
三中 0 0(所影响的行数为 3 行)
*/
T.school_name,
ISNULL(T.grade_count.0)grade_count,
ISNULL(T1.class_count.0)class_count
FROM
(SELECT
school_name ,
school_id ,
COUNT(school_id )grade_count
FROM
tbSchool JOIN tbGrade ON tbSchool.id=tbGrade.school_id
GROUP BY school_name,school_id )AS T
JOIN
(SELECT
school_id ,
COUNT(grade_id )class_count
FROM
tbClass JOIN tbGrade ON tbClass . grade_id =tbGrade.school_id
GROUP BY school_id,grade_id)AS T1
ON T.school_name=T1.school_name
declare @tbSchool table(id int, school_name varchar(10))insert @tbSchool select 1, '一中'
insert @tbSchool select 2, '二中'
insert @tbSchool select 3 , '三中' declare @tbGrade table(id int, school_id int, grade_name varchar(10)) insert @tbGrade select 1 , 1 , '初一'
insert @tbGrade select 2 , 1 , '初二'
insert @tbGrade select 3 , 1 , '初三'
insert @tbGrade select 4 , 1 , '高一'
insert @tbGrade select 5 , 1 , '高二'
insert @tbGrade select 6 , 1 , '高三'
insert @tbGrade select 7 , 2 , '初一'
insert @tbGrade select 8 , 2 , '初二'
insert @tbGrade select 9 , 2 , '初三'
insert @tbGrade select 10 , 2 , '高一'
insert @tbGrade select 11, 2 , '高二'
insert @tbGrade select 12 , 2 , '高三' declare @tbClass table(id int, grade_id int, class_name varchar(10))insert @tbClass select 1 , 1 , '一班'
insert @tbClass select 2 , 1 , '二班'
insert @tbClass select 3 , 1 , '三班'
insert @tbClass select 4 , 1 , '四班'
insert @tbClass select 5 , 2 , '一班'
insert @tbClass select 6 , 2 , '二班'
insert @tbClass select 7 , 2 , '三班'
insert @tbClass select 8 , 2 , '四班'
insert @tbClass select 9 , 3 , '一班'
insert @tbClass select 10, 3 , '二班'
insert @tbClass select 11 , 3 , '三班'
insert @tbClass select 12 , 3 , '四班' SELECT
T.school_name,
ISNULL(T.grade_count,0)grade_count,
ISNULL(T1.class_count,0)class_countFROM (SELECT
school_name ,
school_id ,
COUNT(school_id )grade_count
FROM
@tbSchool TBS LEFT JOIN @tbGrade TBG ON TBS.id=TBG.school_id
GROUP BY school_name,school_id)AS TLEFT JOIN (SELECT
school_id,
COUNT(grade_id )class_count
FROM
@tbClass TBC LEFT JOIN @tbGrade TBG ON TBC .grade_id =TBG.ID
GROUP BY school_id)AS T1
ON T.school_id=T1.school_idORDER BY grade_countschool_name grade_count class_count
----------- ----------- -----------
一中 6 12
二中 6 0
三中 0 0