请教下过滤重复数据库记录的问题
现在我的数据表custom里有字段 UserName,ItemID,Address,此3个字段联合唯一
数据库里有数据:
john 000 shanghai
john 001 shanghai
kate 000 wuhan
kate 001 wuhan
现在我想用一句SQL代码查询,查询结果为:
john 000 shanghai
kate 000 wuhan
即过滤掉重复的人名
我用的sql语句为:
select * from custom a where not exists(select 1 from Custom where UserName=a.UserName)
但是不管用,结果还是显示所有数据。
请各位路过大侠帮帮忙。
------解决方案--------------------if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(UserName varchar(10),ItemID varchar(10),Address varchar(10))
insert into tb(UserName,ItemID,Address) values( 'john ', '000 ', 'shanghai ')
insert into tb(UserName,ItemID,Address) values( 'john ', '001 ', 'shanghai ')
insert into tb(UserName,ItemID,Address) values( 'kate ', '000 ', 'wuhan ')
insert into tb(UserName,ItemID,Address) values( 'kate ', '001 ', 'wuhan ')
go
select a.* from tb a,
(select UserName , Min(ItemID) ItemID from tb group by UserName) b
where a.UserName = b.UserName and a.ItemID = b.ItemID
drop table tb
/*
UserName ItemID Address
---------- ---------- ----------
john 000 shanghai
kate 000 wuhan
(所影响的行数为 2 行)
*/