表1:
id name type price
1 a 1 10
2 b 1 20
3 c 2 10
4 d 3 30
5 e 3 20
怎样写sql语句,查询表的所有字段,但是type相同的只显示一条
即结果应该是:id name type price
1 a 1 10
3 c 2 10
4 d 3 30
id name type price
1 a 1 10
2 b 1 20
3 c 2 10
4 d 3 30
5 e 3 20
怎样写sql语句,查询表的所有字段,但是type相同的只显示一条
即结果应该是:id name type price
1 a 1 10
3 c 2 10
4 d 3 30
from tb t where exists(
select 1 from tb where t.type=type and t.id>id)
where id = (select min(id) from [表A] where type = t.type )
declare @TYPE table(ID int,[name] varchar(1),[type] int,[price] int)
insert @TYPE
select 1,'a',1,10 union all
select 2,'b',1,20 union all
select 3,'c',2,10 union all
select 4,'d',3,30 union all
select 5,'e',3,20SELECT * FROM @TYPE AS T WHERE ID IN(SELECT TOP 1 ID FROM @TYPE WHERE [type]=T.[type] ORDER BY ID )
use practiceDB
go
create table tb1
(
id int,
name char(2),
type int,
price int
)
insert tb1
select 1,'a',1,10 union all
select 2,'b',1,20 union all
select 3,'c',2,10 union all
select 4,'d',3,30 union all
select 5,'e',3,20select * from tb1 t
where id = (select min(id) from tb1 where type = t.type )--result
id name type price
------------------------
1 a 1 10
3 c 2 10
4 d 3 30