去看书或联机帮助.那里有说明. SELECT c.ClassNo,COUNT(a.StuNo),SUM(b.TheValue) FROM TblA a right JOIN TblB b ON b.StuNo=a.StuNo left JOIN TblC c ON c.ClassNo=a.ClassNo GROUP BY c.ClassNo 就不会TheValue重复几次了.因为这样就是以tblB为中心连接了.
SELECT c.ClassNo,COUNT(a.StuNo),(select SUM(TheValue) from tblb where stuno in(select stuno from tblc where tblc.classno=c.classno)) as sumValue FROM TblA a INNER JOIN TblB b ON b.StuNo=a.StuNo INNER JOIN TblC c ON c.ClassNo=a.ClassNo GROUP BY c.ClassNo
--用这个: select c.ClassNo,a.计数,b.数量 from( select stuno,计数=count(stuno) from TblA group by stuno ) a INNER join ( select stuno,数量=sum(TheValue) from TblB group by stuno ) b on a.StuNo=b.StuNo INNER JOIN TblC c ON c.StuNo=a.ClassNo
--这样应该正确了. select c.ClassNo,sum(a.计数),sum(b.数量) from( select stuno,计数=count(distinct stuno) from TblA group by stuno ) a INNER join ( select stuno,数量=sum(TheValue) from TblB group by stuno ) b on a.StuNo=b.StuNo INNER JOIN TblC c ON c.StuNo=a.StuNo group by c.ClassNo
--例子 declare @TblA table(StuNo int,WhoIsChecked bit,TheDate datetime)--主键StuNo,TheDate insert into @tbla select 1,1,'2003-01-01' union all select 1,0,'2003-01-02' union all select 2,0,'2003-01-02' union all select 3,1,'2003-01-03' union all select 4,1,'2003-01-04'declare @TblB table(StuNo int,TheValue int) --主键StuNo insert into @TblB select 1,10 union all select 2,100 union all select 3,1000declare @TblC table(ClassNo int,StuNo int,SomeOther int) --主键ClassNo,StuNo insert into @tblc select 1,1,1 union all select 1,2,1 union all select 2,2,1 union all select 2,3,1 union all select 2,4,1--查询 select c.ClassNo,sum(a.计数),sum(b.数量) from( select stuno,计数=count(distinct stuno) from @TblA group by stuno ) a INNER join ( select stuno,数量=sum(TheValue) from @TblB group by stuno ) b on a.StuNo=b.StuNo INNER JOIN @TblC c ON c.StuNo=a.StuNo group by c.ClassNo/*--结果 ClassNo ----------- ----------- ----------- 1 2 110 2 2 1100(所影响的行数为 2 行)--*/
SELECT c.ClassNo,COUNT(a.StuNo),SUM(b.TheValue)
FROM TblA a right JOIN TblB b ON b.StuNo=a.StuNo
left JOIN TblC c ON c.ClassNo=a.ClassNo
GROUP BY c.ClassNo
就不会TheValue重复几次了.因为这样就是以tblB为中心连接了.
FROM TblA a INNER JOIN TblB b ON b.StuNo=a.StuNo
INNER JOIN TblC c ON c.ClassNo=a.ClassNo
GROUP BY c.ClassNo
select c.ClassNo,a.计数,b.数量
from(
select stuno,计数=count(stuno) from TblA group by stuno
) a INNER join (
select stuno,数量=sum(TheValue) from TblB group by stuno
) b on a.StuNo=b.StuNo
INNER JOIN TblC c ON c.StuNo=a.ClassNo
select c.ClassNo,sum(a.计数),sum(b.数量)
from(
select stuno,计数=count(distinct stuno) from TblA group by stuno
) a INNER join (
select stuno,数量=sum(TheValue) from TblB group by stuno
) b on a.StuNo=b.StuNo
INNER JOIN TblC c ON c.StuNo=a.StuNo
group by c.ClassNo
declare @TblA table(StuNo int,WhoIsChecked bit,TheDate datetime)--主键StuNo,TheDate
insert into @tbla
select 1,1,'2003-01-01'
union all select 1,0,'2003-01-02'
union all select 2,0,'2003-01-02'
union all select 3,1,'2003-01-03'
union all select 4,1,'2003-01-04'declare @TblB table(StuNo int,TheValue int) --主键StuNo
insert into @TblB
select 1,10
union all select 2,100
union all select 3,1000declare @TblC table(ClassNo int,StuNo int,SomeOther int) --主键ClassNo,StuNo
insert into @tblc
select 1,1,1
union all select 1,2,1
union all select 2,2,1
union all select 2,3,1
union all select 2,4,1--查询
select c.ClassNo,sum(a.计数),sum(b.数量)
from(
select stuno,计数=count(distinct stuno) from @TblA group by stuno
) a INNER join (
select stuno,数量=sum(TheValue) from @TblB group by stuno
) b on a.StuNo=b.StuNo
INNER JOIN @TblC c ON c.StuNo=a.StuNo
group by c.ClassNo/*--结果
ClassNo
----------- ----------- -----------
1 2 110
2 2 1100(所影响的行数为 2 行)--*/