select name from student s where age=(select max(age) from student where sex=s.sex)
表student 字段id,name,sex,age现在要显示年龄最大的男人和女人的名字select a.* from student a, (select sex,min(age) age from student group by sex) b where a.sex = b.sex and a.age = b.age
--最大,用max select a.* from student a, (select sex,max(age) age from student group by sex) b where a.sex = b.sex and a.age = b.age
select a.* from student s where age=(select max(age) from student where sex=s.sex)
表student 字段id,name,sex,age现在要显示年龄最大的男人和女人的名字 ---------------select name,sex,max(age) from student group by name,sexselect name from ( select name,sex,max(age) from student group by name,sex ) a
select * from student where sex='男' and age in (select max(age) from student where sex='男') union select * from student where sex='女' and age in (select max(age) from student where sex='女')
declare @a table(id int identity(1,1),name varchar(20),sex bit,age int) insert @a select 'a',1,10 union all select 'b',1,20 union all select 'c',0,10 union all select 'd',0,20select * from @a where age in ( select max(age) as age from @a group by sex)/* (所影响的行数为 4 行)id name sex age ----------- -------------------- ---- ----------- 2 b 1 20 4 d 0 20(所影响的行数为 2 行)*/
declare @a table(id int identity(1,1),name varchar(20),sex bit,age int) insert @a select 'a',1,10 union all select 'b',1,20 union all select 'c',0,10 union all select 'd',0,20 select sex,max(age)from @a group by sex 小楼,俺这样可以不?
create table a ( [id] int, sex bit, age int ) goinsert a select 1,1,45 union all select 2,1,26 union all select 3,0,10 union all select 4,0,55 union all select 5,1,56 union all select 6,0,77 goselect a.* from a, (select sex,max(age) age from a group by a.sex) b where a.sex = b.sex and a.age = b.age go drop table a go(所影响的行数为 6 行)id sex age ----------- ---- ----------- 5 1 56 6 0 77(所影响的行数为 2 行)
TO: bluebullet()可以啊,没错。不过楼主要显示“年龄最大的男人和女人的名字”
select sex,max(age) age from a group by a.sex
create table a ( [id] int, sex bit, name char(12), age int ) goinsert a select 1,1,'cc',45 union all select 2,1,'aa',26 union all select 3,0,'45',10 union all select 4,0,'sa',55 union all select 5,1,'yu',56 union all select 6,0,'jjb',77 goselect a.name from a a, (select sex,max(age) age from a group by sex) b where a.sex = b.sex and a.age = b.age go drop table a go 所影响的行数为 6 行)name ------------ yu jjb (所影响的行数为 2 行)
刚才我写的有错误 : 下面的才对:---------------------------------------------------------------------- declare @a table(id int identity(1,1),name varchar(20),sex bit,age int) insert @a select 'a',1,10 union all select 'b',1,20 union all select 'c',0,10 union all select 'd',0,20 union all select 'e',1,30 union all select 'f',0,30 union all select 'g',1,40 union all select 'h',0,40 union all select 'i',1,50select * from @aselect * from @a a , (select max(age) as age,sex from @a group by sex) b where a.age=b.age and a.sex=b.sex /* (所影响的行数为 9 行)id name sex age ----------- -------------------- ---- ----------- 1 a 1 10 2 b 1 20 3 c 0 10 4 d 0 20 5 e 1 30 6 f 0 30 7 g 1 40 8 h 0 40 9 i 1 50(所影响的行数为 9 行)id name sex age age sex ----------- -------------------- ---- ----------- ----------- ---- 9 i 1 50 50 1 8 h 0 40 40 0(所影响的行数为 2 行) */
declare @a table(id int identity(1,1),name varchar(20),sex bit,age int) insert @a select 'a',1,10 union all select 'b',1,20 union all select 'c',0,10 union all select 'd',0,20 union all select 'e',1,30 union all select 'f',0,30 union all select 'g',1,40 union all select 'h',0,40 union all select 'i',1,50select * from @a select * from @a a where a.age=(select max(age) as age from @a b where a.sex=b.sex group by sex) /*id name sex age ----------- -------------------- ---- ----------- 8 h 0 40 9 i 1 50(所影响的行数为 2 行) */ 这样也可以!!
create table student ( id int, name varchar(12), sex bit, age int )insert into student select 1,'AA01',1,20 union all select 2,'AA02',1,23 union all select 3,'AA03',0,26 union all select 4,'AA04',0,18 union all select 5,'AA05',1,29Select _a.name from student _a inner join (select sex,max(age) as maxage from student group by sex) _b on _a.sex=_b.sex and _a.age=_b.maxage
这么复杂么? select * from a where id =(select id from a where sex='男' and sex in (select max(sex) from a where sex='男')) or id=(select id from a where sex='女'and sex in (select max(sex) from a where sex='女'))
SELECT student.* FROM student, (select sex,max(age) age from student group by sex) A WHERE student.sex = A.sex AND student.age = A.age
select a.* from a, (select sex, max(age) age from a group by a.sex) b where a.sex = b.sex and a.age = b.age受教了.
select s.sex,name from student s join ( select sex,max(age) as agenew from student group by sex) a on a.agenew=s.age
where age=(select max(age) from student where sex=s.sex)
字段id,name,sex,age现在要显示年龄最大的男人和女人的名字select a.* from student a,
(select sex,min(age) age from student group by sex) b
where a.sex = b.sex and a.age = b.age
select a.* from student a,
(select sex,max(age) age from student group by sex) b
where a.sex = b.sex and a.age = b.age
where age=(select max(age) from student where sex=s.sex)
字段id,name,sex,age现在要显示年龄最大的男人和女人的名字
---------------select name,sex,max(age) from student group by name,sexselect name from
(
select name,sex,max(age) from student group by name,sex
) a
union
select * from student where sex='女' and age in (select max(age) from student where sex='女')
insert @a
select 'a',1,10
union all
select 'b',1,20
union all
select 'c',0,10
union all
select 'd',0,20select * from @a where age in (
select max(age) as age from @a group by sex)/*
(所影响的行数为 4 行)id name sex age
----------- -------------------- ---- -----------
2 b 1 20
4 d 0 20(所影响的行数为 2 行)*/
insert @a
select 'a',1,10
union all
select 'b',1,20
union all
select 'c',0,10
union all
select 'd',0,20
select sex,max(age)from @a group by sex
小楼,俺这样可以不?
(
[id] int,
sex bit,
age int
)
goinsert a
select 1,1,45
union all
select 2,1,26
union all
select 3,0,10
union all
select 4,0,55
union all
select 5,1,56
union all
select 6,0,77
goselect a.* from a,
(select sex,max(age) age from a group by a.sex) b
where a.sex = b.sex and a.age = b.age
go
drop table a
go(所影响的行数为 6 行)id sex age
----------- ---- -----------
5 1 56
6 0 77(所影响的行数为 2 行)
(
[id] int,
sex bit,
name char(12),
age int
)
goinsert a
select 1,1,'cc',45
union all
select 2,1,'aa',26
union all
select 3,0,'45',10
union all
select 4,0,'sa',55
union all
select 5,1,'yu',56
union all
select 6,0,'jjb',77
goselect a.name from a a,
(select sex,max(age) age from a group by sex) b
where a.sex = b.sex and a.age = b.age
go
drop table a
go
所影响的行数为 6 行)name
------------
yu
jjb (所影响的行数为 2 行)
下面的才对:----------------------------------------------------------------------
declare @a table(id int identity(1,1),name varchar(20),sex bit,age int)
insert @a
select 'a',1,10
union all
select 'b',1,20
union all
select 'c',0,10
union all
select 'd',0,20
union all
select 'e',1,30
union all
select 'f',0,30
union all
select 'g',1,40
union all
select 'h',0,40
union all
select 'i',1,50select * from @aselect * from @a a ,
(select max(age) as age,sex from @a group by sex) b
where a.age=b.age and a.sex=b.sex
/*
(所影响的行数为 9 行)id name sex age
----------- -------------------- ---- -----------
1 a 1 10
2 b 1 20
3 c 0 10
4 d 0 20
5 e 1 30
6 f 0 30
7 g 1 40
8 h 0 40
9 i 1 50(所影响的行数为 9 行)id name sex age age sex
----------- -------------------- ---- ----------- ----------- ----
9 i 1 50 50 1
8 h 0 40 40 0(所影响的行数为 2 行)
*/
insert @a
select 'a',1,10
union all
select 'b',1,20
union all
select 'c',0,10
union all
select 'd',0,20
union all
select 'e',1,30
union all
select 'f',0,30
union all
select 'g',1,40
union all
select 'h',0,40
union all
select 'i',1,50select * from @a
select * from @a a where a.age=(select max(age) as age from @a b where a.sex=b.sex group by sex)
/*id name sex age
----------- -------------------- ---- -----------
8 h 0 40
9 i 1 50(所影响的行数为 2 行)
*/
这样也可以!!
(
id int,
name varchar(12),
sex bit,
age int
)insert into student
select 1,'AA01',1,20
union all
select 2,'AA02',1,23
union all
select 3,'AA03',0,26
union all
select 4,'AA04',0,18
union all
select 5,'AA05',1,29Select _a.name from student _a inner join
(select sex,max(age) as maxage from student group by sex) _b on _a.sex=_b.sex and _a.age=_b.maxage
FROM student,
(select sex,max(age) age from student group by sex) A
WHERE student.sex = A.sex
AND student.age = A.age
from a, (select sex, max(age) age from a group by a.sex) b
where a.sex = b.sex
and a.age = b.age受教了.