28,391
社区成员
发帖
与我相关
我的任务
分享
declare @t table (name varchar(20),address varchar(20),age int)
insert into @t select '张三','山东',23
insert into @t select '王五','山东',27
insert into @t select '李四','河北',23
insert into @t select '陈六','河北',27
--第二种
select * from @t a where not exists(select 1 from @t where address=a.address and age <a.age)
--第三种
select a.* from @t a left join (select address,min(age) as age from @t group by address) b
on a.address = b.address where b.age=a.age
/*name address age
-------------------- -------------------- -----------
张三 山东 23
李四 河北 23
--年龄最大
select * from @t a where age = (select max(age) from @t where address=a.address)
--顺序去第一条
select * from @t a where age = (select top 1 from @t where address=a.address )
--随机去第一条
select * from @t a where age = (select top 1 from @t where address=a.address order by newid())
declare @t table (name varchar(20),address varchar(20),age int)
insert into @t select '张三','山东',23
insert into @t select '王五','山东',27
insert into @t select '李四','河北',23
insert into @t select '陈六','河北',27
select * from @t a where age = (select min(age) from @t where address=a.address) order by name
---
/*name address age
-------------------- -------------------- -----------
李四 河北 23
张三 山东 23
<%
SELECT TABLENAME.姓名,T1.籍贯,t1.A FROM TABLENAME,(SELECT 籍贯,MIN(TABLENAME.年龄) AS A FROM TABLENAME GROUP BY TABLENAME.籍贯) AS T1 WHERE TABLENAME.籍贯=T1.籍贯 AND TABLENAME.年龄=t1.A
%>
select A.*
from T A
inner join
(select 籍贯,min(年龄) as 年龄
from T group by 籍贯) B
on A.籍贯=B.籍贯 and A.年龄=B.年龄
--更正原来的代码,这个可以在mssql运行
select tempTB.* from
(
select * from 表名称 where 籍贯='山东' and 年龄=(select min(年龄) from 表名称 where 籍贯='山东')
union
select * from 表名称 where 籍贯='河北' and 年龄=(select min(年龄) from 表名称 where 籍贯='河北')
) as tempTB
--ACCESS
select * from
(
select * from tb where 籍贯='山东' and 年龄=(select min(年龄) from 表名称 where 籍贯='山东')
union
select * from tb where 籍贯='河北' and 年龄=(select min(年龄) from 表名称 where 籍贯='河北')
)