group by 用法？
班级表 #a
id 班级
------------
1 一班
2 二班
3 三班
同学表 #b
id name b_id(班级id）
---------------------------
1 aa 1
2 bb 1
3 cc 2
4 dd 3
5 ff 3
6 gg 3
成绩表 #c
id 语文 数学 s_id(同学id)
--------------------------------
1 11 22 6
2 33 44 5
3 55 66 4
4 77 88 3
5 99 12 2
6 24 100 1
问题一：求各班数学成绩最好的同学 （展示表字段 ： 班级 同学名字 数学成绩）
问题二：求各班数学成绩最好的同学、各班语文成绩最好的同学
（展示表字段： 班级 数学最好的同学名称 数学成绩 语文最好的同学名称 语文成绩）
[解决办法]
if object_id('[TBa]') is not null drop table [TBa]gocreate table [TBa] (id int,班级 nvarchar(4))insert into [TBa]select 1,'一班' union allselect 2,'二班' union allselect 3,'三班'if object_id('[TBb]') is not null drop table [TBb]gocreate table [TBb] (id int,name nvarchar(4),b_id int)insert into [TBb]select 1,'aa',1 union allselect 2,'bb',1 union allselect 3,'cc',2 union allselect 4,'dd',3 union allselect 5,'ff',3 union allselect 6,'gg',3if object_id('[TBc]') is not null drop table [TBc]gocreate table [TBc] (id int,语文 int,数学 int,s_id int)insert into [TBc]select 1,11,22,6 union allselect 2,33,44,5 union allselect 3,55,66,4 union allselect 4,77,88,3 union allselect 5,99,12,2 union allselect 6,24,100,1select * from [TBa]select * from [TBb]select * from [TBc]WITH ttAS(SELECT a.班级,b.NAME,c.数学FROM TBc cINNER JOIN TBb b ON c.s_id = b.idINNER JOIN TBa a ON a.id =b.b_id)SELECT a.*FROM tt aWHERE NOT EXISTS(SELECT 1 FROM tt WHERE a.班级 = tt.班级 AND a.数学 <tt.数学)ORDER BY a.班级/*班级 NAME 数学二班 cc 88三班 dd 66一班 aa 100*/
[解决办法]
WITH ttAS(SELECT a.班级,b.NAME,c.数学,c.语文FROM TBc cINNER JOIN TBb b ON c.s_id = b.idINNER JOIN TBa a ON a.id =b.b_id)SELECT a.班级,a.NAME AS '数学高分者',a.数学,b.NAME AS '语文高分者',b.语文 FROM (SELECT a.班级,a.NAME,a.数学FROM tt aWHERE NOT EXISTS(SELECT 1 FROM tt WHERE a.班级 = tt.班级 AND a.数学 <tt.数学)) aINNER JOIN (SELECT a.班级,a.NAME,a.语文FROM tt aWHERE NOT EXISTS(SELECT 1 FROM tt WHERE a.班级 = tt.班级 AND a.语文 <tt.语文)) B ON a.班级 =b.班级/*班级 数学高分者 数学 语文高分者 语文一班 aa 100 bb 99二班 cc 88 cc 77三班 dd 66 dd 55*/
[解决办法]
Declare @A Table (ID int, Name Varchar(20))Insert Into @A Select 1, '一班'Union All Select 2, '二班'Union All Select 3, '三班'Declare @B Table (ID Int, Name Varchar(20), B_ID Int)Insert Into @B (ID, Name, B_ID)Select 1, 'AA', 1Union All Select 2, 'BB', 1Union All Select 3, 'CC', 2Union All Select 4, 'DD', 3Union All Select 5, 'FF', 3Union All Select 6, 'GG', 3Declare @C Table (ID Int, Chinese Int, Math Int, S_Id Int)Insert Into @CSelect 1, 11, 22, 6Union ALL Select 2, 33, 44, 5Union ALL Select 3, 55, 66, 4Union ALL Select 4, 77, 88, 3Union ALL Select 5, 99, 12, 2Union ALL Select 6, 24, 100, 1--问题一：求各班数学成绩最好的同学 （展示表字段 ： 班级 同学名字 数学成绩）Select A.Name ClassName, B.Name StuName, C.Math From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.IDAnd Exists (Select Name, MaxMath From ( Select A.Name, MAX(C.Math) MaxMath From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.ID Group By A.Name) D Where A.Name=D.Name And C.Math=D.MaxMath )/*ClassName StuName Math-------------------- -------------------- -----------一班 AA 100二班 CC 88三班 DD 66*/--求各班语文成绩最好的同学 Select A.Name ClassName, B.Name StuName, C.Chinese From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.IDAnd Exists (Select Name, MaxChinese From ( Select A.Name, MAX(C.Chinese) MaxChinese From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.ID Group By A.Name) D Where A.Name=D.Name And C.Chinese=D.MaxChinese )/*ClassName StuName Chinese-------------------- -------------------- -----------一班 BB 99二班 CC 77三班 DD 55*/ --问题二：求各班数学成绩最好的同学、各班语文成绩最好的同学 Select T_Chinese.ClassName, T_Chinese.StuName, T_Chinese.Chinese, T_Math.ClassName, T_Math.Math From(Select A.Name ClassName, B.Name StuName, C.Chinese From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.IDAnd Exists (Select Name, MaxChinese From ( Select A.Name, MAX(C.Chinese) MaxChinese From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.ID Group By A.Name) D Where A.Name=D.Name And C.Chinese=D.MaxChinese ))As T_Chinese ,(Select A.Name ClassName, B.Name StuName, C.Math From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.IDAnd Exists (Select Name, MaxMath From ( Select A.Name, MAX(C.Math) MaxMath From @C C, @B B, @A A Where C.S_Id=B.ID And B.B_ID=A.ID Group By A.Name) D Where A.Name=D.Name And C.Math=D.MaxMath )) As T_Math Where T_Chinese.ClassName=T_Math.ClassName/*ClassName StuName Chinese ClassName Math-------------------- -------------------- ----------- -------------------- -----------一班 BB 99 一班 100三班 DD 55 三班 66二班 CC 77 二班 88*/
[解决办法]
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2012-08-09 11:51:39-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[a]if object_id('[a]') is not null drop table [a]go create table [a]([id] int,[班级] varchar(4))insert [a]select 1,'一班' union allselect 2,'二班' union allselect 3,'三班'--> 测试数据：[b]if object_id('[b]') is not null drop table [b]go create table [b]([id] int,[name] varchar(2),[b_id] int)insert [b]select 1,'aa',1 union allselect 2,'bb',1 union allselect 3,'cc',2 union allselect 4,'dd',3 union allselect 5,'ff',3 union allselect 6,'gg',3--> 测试数据：[c]if object_id('[c]') is not null drop table [c]go create table [c]([id] int,[语文] int,[数学] int,[s_id] int)insert [c]select 1,11,22,6 union allselect 2,33,44,5 union allselect 3,55,66,4 union allselect 4,77,88,3 union allselect 5,99,12,2 union allselect 6,24,100,1--------------开始查询--------------------------;with f as(select a.班级,b.name,c.数学,c.语文from a,b,cwhere a.id=b.b_idand b.id=c.s_id)select a.*,b.name,b.语文 from (select * from f t where 数学=(select max(数学) from f where 班级=t.班级))a join (select * from f t where 语文=(select max(语文) from f where 班级=t.班级))bon a.班级=b.班级----------------结果----------------------------/* 班级 name 数学 语文 name 语文---- ---- ----------- ----------- ---- -----------一班 aa 100 24 bb 99二班 cc 88 77 cc 77三班 dd 66 55 dd 55(3 行受影响)*/
[解决办法]
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2012-08-09 11:51:39-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[a]if object_id('[a]') is not null drop table [a]go create table [a]([id] int,[班级] varchar(4))insert [a]select 1,'一班' union allselect 2,'二班' union allselect 3,'三班'--> 测试数据：[b]if object_id('[b]') is not null drop table [b]go create table [b]([id] int,[name] varchar(2),[b_id] int)insert [b]select 1,'aa',1 union allselect 2,'bb',1 union allselect 3,'cc',2 union allselect 4,'dd',3 union allselect 5,'ff',3 union allselect 6,'gg',3--> 测试数据：[c]if object_id('[c]') is not null drop table [c]go create table [c]([id] int,[语文] int,[数学] int,[s_id] int)insert [c]select 1,11,22,6 union allselect 2,33,44,5 union allselect 3,55,66,4 union allselect 4,77,88,3 union allselect 5,99,12,2 union allselect 6,24,100,1--------------开始查询--------------------------;with f as(select a.班级,b.name,c.数学 from a,b,cwhere a.id=b.b_idand b.id=c.s_id) select * from f t where 数学=(select max(数学) from f where 班级=t.班级) ----------------结果----------------------------/*班级 name 数学---- ---- -----------一班 aa 100二班 cc 88三班 dd 66(3 行受影响)*/