数据库中表tb_A有3个字段:
ID int IDENTITY (1,1) PRIMARY KEY,
A INT NOT NULL,
B NVARCHAR (50)
问题是这样:
如果我需要查询出来tb_A中的A字段信息,按照ID进行倒序排列,A字段中有重复字段,要求去除重复。
请各位大侠帮忙看看该怎么写
ID int IDENTITY (1,1) PRIMARY KEY,
A INT NOT NULL,
B NVARCHAR (50)
问题是这样:
如果我需要查询出来tb_A中的A字段信息,按照ID进行倒序排列,A字段中有重复字段,要求去除重复。
请各位大侠帮忙看看该怎么写
select min(id) id , A from tb_a group by A--包含B
select a.* from tb_a where id = (select min(id) from tb_a group by a)
select a.* from tb_a where id = (select min(id) from tb_a group by a) select a.* from tb_a a,
(select a,min(id) id from tb_a group by a) b
where a.id = b.id and a.a = b.aselect a.* from tb_a where id in (select min(id) from tb_a group by a)
select StudentID from t2 group by StudentID order by min(CourseID) desc保留大的,按ID大的排序
select StudentID from t2 group by StudentID order by max(CourseID) desc
select A from tb_A group by A order by min(ID) desc 保留大的,按ID大的排序
select A from tb_A group by A order by max(ID) desc
order by ID desc--保留A重复ID最小的条记录select * from tb_A t where not exists(select 1 from tb_a where a=t.a and ID>ID)
order by ID desc--保留A重复ID最大的条记录
select * from tb_A a inner join (select min(id) id from tb_a group by A ) b on a.ID=b.ID
c1 c2 type
第1行 a 9 5
第2行 b 8 5
第3行 c 7 6
第4行 d 6 6
第5行 e 5 7
--@Test
declare @test table(c1 varchar(1),c2 int,type int)
insert @test
select 'a',9,5 union all
select 'b',8,5 union all
select 'c',7,6 union all
select 'd',6,6 union all
select 'e',5,7--按记录顺序取第一条
select * from @test a where c1=(select top 1 c1 from @Test where type=a.type)--取最小
select * from @test a where c1=(select min(c1) from @Test where type=a.type)--取最大
select * from @test a where c1=(select max(c1) from @Test where type=a.type)--随机取
select * from @test a where c1=(select top 1 c1 from @Test where type=a.type order by newid())