重新发过,漏了汇总的

Question

表一: 
班级    平均成绩  总成绩 
高一      78.2      3907.7 
1        75.6      3780 
2        80.4      3939.6 
3        78.5      4003.5 
...      ...        ... 表二是查询得到的派生表 
id      大于80分的男生 
101001001      张翔 
101001003      何劲 
101001008      向超 
..... 
101002002      杨帆 
101002007      陈力 
101002009      刘宗 
..... 
101003010      张天白 
101003015      何轩农 
101003017      林翔 
.... 
表一\表二的关系:101开头的代表高一 ,101001开头的代表1班,101002开头的代表2班... 
现要得到下表,这个SQL要怎么写? 
班级    平均成绩  总成绩        大于80分的男生人数 
高一      78.2    3907.7          9 
1        75.6      3780            3 
2        80.4      3939.6          3 
3        78.5      4003.5          3 

Answer 1

SELECT 
班级='高一',
AVG(平均成绩)平均成绩,
SUM(  总成绩  ) 总成绩 ,
SUM(大于80分的男生人数) 大于80分的男生人数FROM (SELECT
    *,
    大于80分的男生人数=(SELECT COUNT(*)
                      FROM tb2
                      WHERE CAST(LEFT(id,3) AS INT) = A.班级)
FROM tb1 AS A)AS TUNION ALL
SELECT
    *,
    大于80分的男生人数=(SELECT COUNT(*)
                      FROM tb2
                      WHERE CAST(LEFT(id,3) AS INT) = A.班级)
FROM tb1 AS A你的班级一定要是字符型的,要不然没法弄

Answer 2

declare @a table(班级 varchar(20),平均成绩 dec(18,2),总成绩 dec(18,2))
insert @a
select '高一',78.2,3907.7 union all
select '1',75.6,3780 union all
select '2',80.4,3939.6 union all
select '3',78.5,4003.5 
declare @b table([id] varchar(10),[>80] varchar(50))
insert @b
select '101001001','张翔' union all 
select '101001003','何劲' union all 
select '101001008','向超' union all 
select '101002002','杨帆' union all 
select '101002007','陈力' union all 
select '101002009','刘宗' union all 
select '101003010','张天白' union all
select '101003015','何轩农' union all 
select '101003017','林翔'select a.*,b.[>80人数] from @a a left join (select (case when id is null then '高一' else id end) id,[>80人数] from (select substring(id,6,1) id,count([>80]) [>80人数] from @b group by substring(id,6,1) with rollup) z) b
on a.班级=b.id
              

Answer 3

SELECT 班级  ,  
平均成绩 , 
总成绩 ,
CASE WHEN 大于80分的男生人数 IS NULL THEN SUM(大于80分的男生人数) ELSE 大于80分的男生人数  END AS '大于80分的男生人数' FROM 
(
SELECT A.* ,
SELECT COUNT(*) FROM B WHERE A.班级=CONVERT(INT,LEFT(B.ID,3)) AS '大于80分的男生人数' 
FROM A LEFT JOIN B ON A.班级=CONVERT(INT,LEFT(B.ID,3)) ) AS T

Answer 4

--建议表一添加年级编号列
declare @a table(年级编号 char(3),班级 varchar(20),平均成绩 dec(18,2),总成绩 dec(18,2))
insert @a
select '101','高一',78.2,3907.7 union all
select '101','1',75.6,3780 union all
select '101','2',80.4,3939.6 union all
select '101','3',78.5,4003.5 
declare @b table([id] varchar(10),[>80] varchar(50))
insert @b
select '101001001','张翔' union all 
select '101001003','何劲' union all 
select '101001008','向超' union all 
select '101002002','杨帆' union all 
select '101002007','陈力' union all 
select '101002009','刘宗' union all 
select '101003010','张天白' union all
select '101003015','何轩农' union all 
select '103001008','向超1' union all 
select '103002002','杨帆1' union all 
select '103002007','陈力1' union all 
select '102002009','刘宗1' union all 
select '102003010','张天白1' union all
select '102003015','何轩农1' union all 
select '101003017','林翔'
select a.*,[>80人数]=b.cnt
from @a A left join 
(
select 
Grade=left(id,3),
id=(case when grouping(0+substring(id,4,3))=1 
then (case left(id,3) 
when '101' then '高一' 
when '102' then '高二' 
when '103' then '高三' end) 
else ltrim(0+substring(id,4,3))
end),
cnt=count(1) 
from @b 
group by 
left(id,3),
0+substring(id,4,3) 
with rollup
having grouping(left(id,3))<>1
) B
on A.班级=B.id and A.年级编号=B.Grade年级编号 班级                   平均成绩                                    总成绩                                     >80人数
---- -------------------- --------------------------------------- --------------------------------------- -----------
101  高一                   78.20                                   3907.70                                 9
101  1                    75.60                                   3780.00                                 3
101  2                    80.40                                   3939.60                                 3
101  3                    78.50                                   4003.50                                 3(4 行受影响)

Answer 5

select
    a.班级,a.平均成绩,a.总成绩,count(b.id) as [大于80分的男生人数]
from
    表一 a
left join
    表二 b
where
    (case a.班级 when '高一' then '101' else '10100'+a.班级 end)=(case a.班级 when '高一' then left(b.id,3) else left(b.id,6) end)
group by
    a.班级,a.平均成绩,a.总成绩

Answer 6

11楼的大哥，提示在关键字‘where’附近有有语法错误。

Answer 7

表一: 
班级    平均成绩  总成绩 
高一      78.2      3907.7 
1        75.6      3780 
2        80.4      3939.6 
3        78.5      4003.5 
这个表是不是有什么问题？有没有高二呢？Select Left(ID,3) 年级,SubString(ID,3,3) 班级,Count(1) 人数 From 表二
Group By Left(ID,3),SubString(ID,3,3)