create table ai(id int,i varchar(10),ii varchar(10), iii varchar(10))
insert into ai select 1, 'AAA', 'C', 'T'
union all select 2, 'AAA', 'D', 'T'
union all select 3, 'BBB', 'C' , 'T'
union all select 4, 'BBB', 'D', 'T'
go
select *
from ai
where id in(
select min(id)
from ai
group by i)
id i ii iii
----------- ---------- ---------- ----------
1 AAA C T
3 BBB C T
create table ai(id int,i varchar(10),ii varchar(10), iii varchar(10))
insert into ai select 1, 'AAA', 'C', 'T'
union all select 2, 'AAA', 'D', 'T'
union all select 3, 'BBB', 'C' , 'T'
union all select 4, 'BBB', 'D', 'T'
union all select 5, 'jjj', 'c', 'n'
union all select 6, 'kkk', 'd', 'i'
go
select *
from ai
where id in(
select min(id) as ids
from ai
group by iii,ii)
create table ai(id int,i varchar(10),ii varchar(10), iii varchar(10))
insert into ai select 1, 'AAA', 'C', 'T'
union all select 2, 'AAA', 'D', 'T'
union all select 3, 'BBB', 'C' , 'T'
union all select 4, 'BBB', 'D', 'T'
union all select 5, 'jjj', 'c', 'n'
union all select 6, 'kkk', 'd', 'i'
go
select a.*
from ai as a join (
select min(id) as ids, ii,iii
from ai
group by iii,ii) as b
on a.id=b.ids
id i ii iii
----------- ---------- ---------- ----------
1 AAA C T
2 AAA D T
5 jjj c n
6 kkk d i
有一个数据表,记录如下:
列1 列2 列3
1 AAA C T
2 AAA D T
3 BBB C T
4 BBB D T
5 JJJ C N
6 KKK D I
首先,SQL语句应该找出指定的条件 WHERE 列3 = T
然后如果列2有相同的纪录则取第一个,比如1 和 3都有C就只返回第一个纪录。
而2 和 4都有D就返回第二个纪录。
create table ai(id int,i varchar(10),ii varchar(10), iii varchar(10))
insert into ai select 1, 'AAA', 'C', 'T'
union all select 2, 'AAA', 'D', 'T'
union all select 3, 'BBB', 'C' , 'T'
union all select 4, 'BBB', 'D', 'T'
go
select *
from ai as a
where id=(select top 1 id from ai where i=a.i order by id)
id i ii iii
----------- ---------- ---------- ----------
1 AAA C T
3 BBB C T