id name age class1 John 12 9902 2 Jack 10 9901 3 Mate 15 9901 4 Tom 5 9902 5 Jason 5 9903找出每个班年龄最小的学生 例子结果id name age class2 Jack 10 9901 4 Tom 5 9902 5 Jason 5 9903我用 select id,name,min( age),class from student group by class 出错,怎么写
declare @a table (id int, name varchar(10) , age int, class int) insert @a select 1, 'John', 12, 9902 union all select 2, 'Jack', 10, 9901 union all select 3, 'Mate', 15, 9901 union all select 4, 'Tom', 5, 9902 union all select 5, 'Jason',5, 9903 select * from @a a where age in (select top 1 age from @a where class=a.class order by age asc)(所影响的行数为 5 行)id name age class ----------- ---------- ----------- ----------- 4 Tom 5 9902 5 Jason 5 9903 2 Jack 10 9901(所影响的行数为 3 行)
declare @a table (id int, name varchar(10) , age int, class int) insert @a select 1, 'John', 12, 9902 union all select 2, 'Jack', 10, 9901 union all select 3, 'Mate', 15, 9901 union all select 4, 'Tom', 5, 9902 union all select 5, 'Jason',5, 9903 select * from @a a where age in (select min(age) from @a where class=a.class group by class) (所影响的行数为 5 行)id name age class ----------- ---------- ----------- ----------- 2 Jack 10 9901 4 Tom 5 9902 5 Jason 5 9903(所影响的行数为 3 行)
select id,name,min( age),class from student group by class 出错,怎么写 id,name 没有在group by 组中所有要出错
insert @a
select 1, 'John', 12, 9902 union all
select 2, 'Jack', 10, 9901 union all
select 3, 'Mate', 15, 9901 union all
select 4, 'Tom', 5, 9902 union all
select 5, 'Jason',5, 9903
select * from @a a where age in (select top 1 age from @a where class=a.class order by age asc)(所影响的行数为 5 行)id name age class
----------- ---------- ----------- -----------
4 Tom 5 9902
5 Jason 5 9903
2 Jack 10 9901(所影响的行数为 3 行)
insert @a
select 1, 'John', 12, 9902 union all
select 2, 'Jack', 10, 9901 union all
select 3, 'Mate', 15, 9901 union all
select 4, 'Tom', 5, 9902 union all
select 5, 'Jason',5, 9903
select * from @a a where age in (select min(age) from @a where class=a.class group by class)
(所影响的行数为 5 行)id name age class
----------- ---------- ----------- -----------
2 Jack 10 9901
4 Tom 5 9902
5 Jason 5 9903(所影响的行数为 3 行)
group by class
出错,怎么写
id,name 没有在group by 组中所有要出错