比如有以下数据记录
id name age password
1 张三 17 123456
2 李四 18 23423422
3 王五 17 237293478
4 赵六 18 233234232
比如我想根据age来查询,如果年龄等于17,就只查询一条记录,张三也好,李四也好,随便哪个都行。给如何做呢
id name age password
1 张三 17 123456
2 李四 18 23423422
3 王五 17 237293478
4 赵六 18 233234232
比如我想根据age来查询,如果年龄等于17,就只查询一条记录,张三也好,李四也好,随便哪个都行。给如何做呢
from tb
where age=17
order by newid()
*
from
tb t
where
id=(select max(id) from tb whre age=t.age)
-- Author: T.O.P
-- Create date: 20091126
-- Version: SQL SERVER 2000
-- =============================================
declare @tb table([id] int,[name] varchar(4),[age] int,[password] int)
insert @tb
select 1,'张三',17,123456 union all
select 2,'李四',18,23423422 union all
select 3,'王五',17,237293478 union all
select 4,'赵六',18,233234232select TOP 1 * from @tb WHERE AGE=17 ORDER BY NEWID()
--测试结果:
/*
id name age password
----------- ---- ----------- -----------
1 张三 17 123456(所影响的行数为 1 行)*/
select top 1 * from tb where age = 17 order by id
select top 1 * from tb where age = 17 order by desc
select top 1 * from tb where age = 17 order by newid()
SELECT MAX(id),MAX(name),age,MAX(password) GROUP BY age
但是对于楼主的这一物例数据表,这种语句下下去没有什么意义。