我用一个查询出来下如下数据
TypeID, TypeName, summery
78 婚纱 124
78 婚纱 33333124
85 婚宴 555555
怎么再从这个查询中得如下数据(去掉重复的,但能summery字段)
TypeID, TypeName, summery
78 婚纱 124
85 婚宴 555555
TypeID, TypeName, summery
78 婚纱 124
78 婚纱 33333124
85 婚宴 555555
怎么再从这个查询中得如下数据(去掉重复的,但能summery字段)
TypeID, TypeName, summery
78 婚纱 124
85 婚宴 555555
from tb
group by TypeID, TypeName
select t.* from tb t where summery = (select min(summery) from tb where TypeID = t.TypeID)
select t.* from tb t where not exists (select 1 from tb where TypeID = t.TypeID and summery < t.summery )
summery应字符类型是文字
summery应字符类型是文字
select TypeID,TypeName,min(summery) from tb group by TypeID,TypeName
否則:
select * from tb t where summery = (select min(summery) from tb where TypeID = t.TypeID)
或
select * from tb t where not exists (select 1 from tb where TypeID = t.TypeID and summery < t.summery )
或
select * from tb t where summery = (select top 1 summery from tb where TypeID = t.TypeID order by summery)
是字符类型
*************************************
* T-MAC 小编 *
* -->努力成长中 *
* -->梦想DBA *
*************************************
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (TypeID int, TypeName varchar(10) ,summery varchar(20))
insert tb select
78, '婚纱', '124' union select
78, '婚纱', '33333124' union select
85, '婚宴', '555555'
go
select *
from tb k
where not exists(select * from tb where k.TypeID=TypeID and summery<k.summery)
/*
TypeID TypeName summery
----------- ---------- --------------------
78 婚纱 124
85 婚宴 555555
*/