sql根据某一个字段重复只取第一条数据 sql中如何使一列中的多个重复数据只显示第一条
\u5728mysql\u6570\u636e\u5e93\u4e2d\u5982\u4f55\u8ba9\u67d0\u4e2a\u5b57\u6bb5\u6709\u91cd\u590d\u7684\u53ea\u53d6\u4e00\u6761\u5b9e\u73b0\u7684\u65b9\u6cd5\u548c\u8be6\u7ec6\u7684\u64cd\u4f5c\u6b65\u9aa4\u5982\u4e0b\uff1a
1\u3001\u7b2c\u4e00\u6b65\uff0c\u4f7f\u7528navicat\u8fde\u63a5\u5230mysql\u6570\u636e\u5e93\u5e76\u521b\u5efa\u4e00\u4e2a\u65b0\u7684\u7528\u6237\u8868\uff0c\u89c1\u4e0b\u56fe\uff0c\u8f6c\u5230\u4e0b\u9762\u7684\u6b65\u9aa4\u3002
2\u3001\u7b2c\u4e8c\u6b65\uff0c\u5b8c\u6210\u4e0a\u8ff0\u6b65\u9aa4\u540e\uff0c\u586b\u5199\u4e00\u4e9b\u6d4b\u8bd5\u5185\u5bb9\u4ee5\u6f14\u793a\u6d4b\u8bd5\u7ed3\u679c\uff0c\u89c1\u4e0b\u56fe\uff0c\u8f6c\u5230\u4e0b\u9762\u7684\u6b65\u9aa4\u3002
3\u3001\u7b2c\u4e09\u6b65\uff0c\u5b8c\u6210\u4e0a\u8ff0\u6b65\u9aa4\u540e\uff0c\u9009\u62e9\u7528\u6237\u540d\uff0c\u7136\u540e\u5355\u51fb\u9f20\u6807\u53f3\u952e\u4ee5\u9009\u62e9\u201c\u8bbe\u8ba1\u8868\u201d\u9009\u9879\uff0c\u89c1\u4e0b\u56fe\uff0c\u8f6c\u5230\u4e0b\u9762\u7684\u6b65\u9aa4\u3002
4\u3001\u7b2c\u56db\u6b65\uff0c\u5b8c\u6210\u4e0a\u8ff0\u6b65\u9aa4\u540e\uff0c\u5207\u6362\u5230\u8bbe\u8ba1\u8868\u4e2d\u7684\u201c\u7d22\u5f15\u201d\u6807\u7b7e\uff0c\u89c1\u4e0b\u56fe\uff0c\u8f6c\u5230\u4e0b\u9762\u7684\u6b65\u9aa4\u3002
5\u3001\u7b2c\u4e94\u6b65\uff0c\u5b8c\u6210\u4e0a\u8ff0\u6b65\u9aa4\u540e\uff0c\u5f00\u59cb\u6dfb\u52a0\u7d22\u5f15\u3002\u5982\u679c\u4e0d\u9700\u8981\u7d22\u5f15\u540d\u79f0\uff0c\u5219\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u53ef\u4ee5\u4e3a\u7a7a\u3002\u8be5\u5de5\u5177\u5c06\u81ea\u52a8\u751f\u6210\u4e0e\u5b57\u6bb5\u540d\u79f0\u76f8\u540c\u7684\u540d\u79f0\u3002\u5355\u51fb\u5b57\u6bb5\u540e\u9762\u7684\u6309\u94ae\u4ee5\u663e\u793a\u9009\u62e9\u6846\uff0c\u9009\u62e9\u9700\u8981\u552f\u4e00\u7ea6\u675f\u7684\u5b57\u6bb5\u3002\u5728\u8fd9\u91cc\uff0c\u767b\u5f55\u5230\u540d\u79f0\u5b57\u6bb5\uff0c\u89c1\u4e0b\u56fe\uff0c\u8f6c\u5230\u4e0b\u9762\u7684\u6b65\u9aa4\u3002
6\u3001\u7b2c\u516d\u6b65\uff0c\u5b8c\u6210\u4e0a\u8ff0\u6b65\u9aa4\u540e\uff0c\u9009\u62e9\u201c\u7d22\u5f15\u7c7b\u578b\u201d\u9009\u9879\uff0c\u552f\u4e00\u7ea6\u675f\u5fc5\u987b\u9009\u62e9\u201cUnique\u201d\u7c7b\u578b\uff0c\u89c1\u4e0b\u56fe\uff0c\u8f6c\u5230\u4e0b\u9762\u7684\u6b65\u9aa4\u3002
7\u3001\u7b2c\u4e03\u6b65\uff0c\u5b8c\u6210\u4e0a\u8ff0\u6b65\u9aa4\u540e\uff0c\u5c06\u7b2c\u4e09\u6761\u6570\u636e\u7684\u767b\u5f55\u540d\u4fee\u6539\u4e3a\u4e0e\u7b2c\u4e8c\u6761\u6570\u636e\u76f8\u540c\uff0c\u7136\u540e\u5355\u51fb\u4e0b\u9762\u7684\u590d\u9009\u6807\u8bb0\u6309\u94ae\u8fdb\u884c\u4fdd\u5b58\uff0c\u89c1\u4e0b\u56fe\uff0c\u8f6c\u5230\u4e0b\u9762\u7684\u6b65\u9aa4\u3002
8\u3001\u7b2c\u516b\u6b65\uff0c\u5b8c\u6210\u4e0a\u8ff0\u6b65\u9aa4\u540e\uff0c\u4fdd\u5b58\u65f6\u5c06\u62a5\u544a\u9519\u8bef\uff0c\u63d0\u793a\u201cDuplicate entry 'bb' for key 'login_name'\u201d\uff0c\u91cd\u590d\u7684\u767b\u5f55\u540d\u65e0\u6cd5\u6210\u529f\u4fdd\u5b58\uff0c\u8868\u660e\u6dfb\u52a0\u7684\u552f\u4e00\u7ea6\u675f\u5df2\u751f\u6548\uff0c\u89c1\u4e0b\u56fe\u3002\u8fd9\u6837\uff0c\u5c31\u89e3\u51b3\u4e86\u8fd9\u4e2a\u95ee\u9898\u4e86\u3002
1\u3001\u9996\u5148\u5728\u5982\u4e0b\u56fe\u4e2d\u662f\u4e00\u4e2aStudent\u7684\u6570\u636e\u8868\uff0c\u8fd9\u91cc\u9700\u8981\u5bf9\u6570\u636e\u8868\u4e2d\u7684StudentName\u6570\u636e\u8868\u4e2d\u7684\u5355\u4e2a\u7684\u6570\u636e\u8fdb\u884c\u4fee\u6539\u3002
2\u3001\u540c\u6837\u7684\u65b9\u6cd5\u8fd9\u91cc\u9996\u5148\u6253\u5f00\u6570\u636e\u5e93\u67e5\u8be2\u8bed\u53e5\uff0c\u8f93\u5165\u67e5\u8be2\u8bed\u53e5\u5148\u67e5\u8be2\u4e00\u4e0b\u6570\u636e\u8868\u4e2d\u7684\u6570\u636e\u5185\u5bb9\u3002
3\u3001\u8f93\u5165\u67e5\u8be2\u8bed\u53e5\uff0c:Select * from Student\uff0c\u8f93\u5165\u5b8c\u6bd5\u4e4b\u540e\u9009\u62e9\u5168\u90e8\u5185\u5bb9\uff0c\u7136\u540e\u9009\u62e9\u6267\u884c\u6309\u94ae\uff0c\u6267\u884cSQL\u8bed\u53e5\u3002
4\u3001\u6267\u884c\u5b8c\u6bd5\u4e4b\u540e\uff0c\u5728Student\u8868\u4e2d\u7684\u7b2c\u56db\u884c\u6570\u636e\uff08StudentName\u4e2d\u7684\u201c\u5d14\u201d\uff09\u8fd9\u91cc\u9700\u8981\u628a\u5b83\u4fee\u6539\u4e3a\u201c\u4eae\u4eae\u201d\uff0c\u6ce8\u610f\u8fd9\u91cc\u53ea\u8981\u4fee\u6539\u7684\u662f\u5355\u4e2a\u6570\u636e\uff0c\u5e76\u4e0d\u662f\u6574\u884c\u6570\u636e\u3002
5\u3001\u5728\u6570\u636e\u8868\u4e0a\uff0c\u7528\u9f20\u6807\u53f3\u51fb\u9009\u62e9\u91cc\u9762\u7684\u201c\u7f16\u8f91\u524d200\u884c\u201d\u3002
6\u3001\u7f16\u8f91\u5b8c\u6bd5\u4e4b\u540e\uff0c\u5c31\u53ef\u4ee5\u5728\u67e5\u8be2\u8868\u4e2d\u770b\u5230\u6240\u6709\u6570\u636e\u4e86\uff0c\u5982\u4e0b\u56fe\u4e2d\u7ea2\u7ebf\u5185\u5bb9\u3002
代码如下：
select * from tbl_DPImg where ID in (select min(ID) from tbl_DPImg group by DPID)
处理后结果为:
查找表中多余的重复记录，重复记录是根据单个字段（teamId）来判断
select * from team where teamId in (select teamId from team group by teamId having count(teamId) > 1)
删除表中多余的重复记录，重复记录是根据单个字段（teamId）来判断，只留有rowid最小的记录
delete from team where
teamName in(select teamName from team group by teamName having count(teamName) > 1)
and teamId not in (select min(teamId) from team group by teamName having count(teamName)>1)
扩展资料
数据记录筛选：
sql="select * from 数据表 where字段名=字段值 order by字段名[desc]"（按某个字段值降序排列。默认升序ASC）
sql="select * from 数据表 where字段名like '%字段值%' order by 字段名 [desc]"
sql="select top 10 * from 数据表 where字段名=字段值 order by 字段名 [desc]"
sql="select top 10 * from 数据表 order by 字段名 [desc]"
sql="select * from 数据表 where字段名in ('值1','值2','值3')"
sql="select * from 数据表 where字段名between 值1 and 值2"
参考资料来源：百度百科：SQL语句大全
使用分析函数row_number() over (partiion by ... order by ...)来进行分组编号，然后取分组标号值为1的记录即可。目前主流的数据库都有支持分析函数，很好用。
其中，partition by 是指定按哪些字段进行分组，这些字段值相同的记录将在一起编号；order by则是指定在同一组中进行编号时是按照怎样的顺序。
示例(SQL Server 2005或以上适用)：
select s.*
from (
select *, row_number() over (partition by [手机号] order by [店铺]) as group_idx
from table_name
) s
where s.group_idx = 1
如果仅仅只是查询出来去从，那么就用distinct
select distinct 需要去重的列明(允许多列) from table
如果是需要在表中删除，可以这样处理
1、建立临时表，将重复记录查询出来去重插入到临时表
2、删除实表中的重复记录
3、将临时表中的记录插入到实表
处理完成
select * into ##tmp_table from 表 where 1=2
declare @PhoneNO int
declare cur cursor for
select 手机号 from 表 group by 手机号
open cur
fetch next from cur into @PhoneNO
while @@fetch_status=0
begin
insert into ##tmp_table
select top 1 from 表 where 手机号=@PhoneNO
fetch next from cur into @PhoneNO
end
select * from ##tmp_table
drop table ##tmp_table
最简单的 select distinct (手机号)
绛旓細select * from team where teamId in (select teamId from team group by teamId having count(teamId) > 1) 鍒犻櫎琛ㄤ腑澶氫綑鐨閲嶅璁板綍锛岄噸澶嶈褰曟槸鏍规嵁鍗曚釜瀛楁锛坱eamId锛夋潵鍒ゆ柇锛屽彧鐣欐湁rowid鏈灏忕殑璁板綍 delete from team where teamName in(select teamName from team group by teamName having ...
绛旓細SELECT DISTINCT(PartentID), Memo FROM TableName 灏辨槸涓绉嶆瘮杈冩湁鏁堢殑鏂规硶锛岃繕鏈夊叾浠栫殑鏂规硶锛SQLSERVER2005涓 WITH Temp AS (SELECT ROW_NUMBER() OVER(PARTITION BY PartentID ORDER BY @@ROWCOUNT) AS ID, PartentID, Memo FROM TableName)SELECT * FROM Temp WHERE ID = 1 涔熸槸涓绉嶅緢濂界殑...
绛旓細SELECT DISTINCT(PartentID), Memo FROM TableName 灏辨槸涓绉嶆瘮杈冩湁鏁堢殑鏂规硶锛岃繕鏈夊叾浠栫殑鏂规硶锛SQLSERVER2005涓 WITH Temp AS (SELECT ROW_NUMBER() OVER(PARTITION BY PartentID ORDER BY @@ROWCOUNT) AS ID, PartentID, Memo FROM TableName)SELECT * FROM Temp WHERE ID = 1 涔熸槸涓绉嶅緢濂界殑...
绛旓細select distinct(B) from A锛涜繖鏍峰氨鍙互浜嗗晩 濡傛灉鏄畬鍏ㄧ浉鍚岀殑涓鏉¤褰曞氨鐢ㄨ繖涓 select top 1 * from A group by B,C,D;
绛旓細鍏堝仛涓涓瑙嗗浘锛氫粎鍙 parentid(鏈閲嶅鐨)涓殑绗竴鏉 鍐嶅仛涓涓氦鍙夋煡璇㈠嵆鍙 渚嬪 琛ˋ 鏈変富閿瓸鍜屼綘鐨瀛楁parentid(鏈夐噸澶嶇殑),memo(娌℃湁閲嶅鐨)瑙嗗浘C锛歴elect parentid,min(b) from a group by parentid 鐒跺悗鐢細 select c.parentid,a.memo from c inner join a on a.b=c.b 涓嶉渶瑕佹父鏍...
绛旓細闇瑕涓涓涓婚敭 id 濡傛灉鏄鍒犻櫎鏄鍚閲嶅鐨勮瘽锛岃瘯璇曚互涓嬩唬鐮 delete ta where id not in ( select max(id) from group by nch )濡傛灉瑕佹樉绀轰笉閲嶅椤圭殑鏁版嵁 select * from ta where id in ( select max(id) from group by 濮撳悕 )濡傛灉 nch ...
绛旓細--鍒ゆ柇涓涓瓧娈甸噸澶灏卞彧鏄剧ず涓鏉★紝鐢╠istinct鏄笉琛岀殑锛屽彲浠ョ敤row_number()鏍规嵁杩欎釜瀛楁鍒嗙粍鏄剧ず浼樺厛绾э紝鐒跺悗鍙彇姣忎釜缁勭殑绗竴鏉elect * from(select *,row_number() over(partition by 鍒ゆ柇閲嶅鐨勫瓧娈靛悕) as f_id from 琛ㄥ悕) twhere f_id =1 ...
绛旓細select top 1 * form 琛2 where id=(select id form 琛1 order by id esc) order by time desc top 1 閫夊嚭绗竴鏉¤褰 order by time desc 鎸夋椂闂村掑簭鎺掑垪,杩欐牱绗竴鏉¤褰曞氨鏄棩鏈熸渶鏅氱殑璁板綍浜 id=(select id form 琛1 order by id esc)琛ㄤ竴涓殑id涓庤〃浜屼腑鐩哥瓑鐨刬d ...
绛旓細112) from a)t1 where datepart(month,timestr)=12 gorup by username order by count(*)濡傛灉闇瑕佹壘姣忓ぉ閮芥湁鐨勮褰曪紝鍙互鍔犱笂having count(*) = 31 t1鍙槸缁 (select distinct username,timestr from a)杩欎釜鏌ヨ璧风殑鍒悕锛屽彲浠ラ殢渚胯捣锛屼綔鐢ㄧ浉褰撲笌鎶婅繖涓煡璇㈢粨鏋滀綔涓涓涓琛ㄥ悕涓簍1鐨勮〃鏉ュ鐞嗐
绛旓細select top 1 type from 琛ㄥ悕 where owner_type='浣犵殑鍊' and player_id='浣犵殑鍊' order by type --desc
