例如:
我现在有个表字段分别是
number name age
001 aa 22
001 bb 33
001 cc 44
002 dd 55
002 rr 55
003 yy 55
003 tt 55
我现在查询出来以后显示的效果是只显示001,002,003各1条纪录不要显示001,002,003的所有纪录。这个好像是1对多的性质吧!
我现在有个表字段分别是
number name age
001 aa 22
001 bb 33
001 cc 44
002 dd 55
002 rr 55
003 yy 55
003 tt 55
我现在查询出来以后显示的效果是只显示001,002,003各1条纪录不要显示001,002,003的所有纪录。这个好像是1对多的性质吧!
where not exists (
select 1 from tablename
where number=a.number and name<a.name
)
select * from tablename a
where name = (
select top 1 name from tablename
where number=a.number
order by name
)
Select
*
From
TableName A
Where Not Exists
(Select 1 From TableName Where Number=A.Number And Age>A.Age )
----你要求相同的ID,Age 最小的
Select
*
From
TableName A
Where Not Exists
(Select 1 From TableName Where Number=A.Number And Age<A.Age )
Select
*
From
TableName A
Where Not Exists
(Select 1 From TableName Where Number=A.Number And Age>A.Age )
----你要求相同的ID,Age 最小的
Select
*
From
TableName A
Where Not Exists
(Select 1 From TableName Where Number=A.Number And Age<A.Age )
========================
要是age相同的话还是会显示重复的值啊
Haiwer(海阔天空)的这个就可以
select * from tablename a
where name = (
select top 1 name from tablename
where number=a.number
order by name
)
Declare @t Table(Number Varchar(4),Name Varchar(10),Age Int)
Insert @t Select '001','aa', 22
Insert @t Select '001','aa', 23 --- 加此一行(Number 和Name相同,但 Age不相同)
Insert @t Select '001','aa', 23 --- 加此一行(Number 和Name和Age都相同)
Union All Select '001','bb', 33
Union All Select '001','cc', 44
Union All Select '002','dd', 55
Union All Select '002','rr', 55
Union All Select '003','yy', 55
Union All Select '003','tt', 55
---查询测试数据
Select * From @t
---借助一下临时表
If Object_ID('Tempdb..#') Is Not Null
Drop Table Tempdb..#
---Go
Select
* Into #
From
@t A
Where Not Exists
(Select 1 From @t Where Number=A.Number And Age<A.Age )
---查询结果
select distinct * from # a
where name = (
select top 1 name from #
where number=a.number
order by name
)/*
Number Name Age
------ ---------- -----------
001 aa 22
002 dd 55
003 tt 55(所影响的行数为 3 行)
*/