www.qmhc.net > sql根据某一个字段重复只取第一条数据

sql根据某一个字段重复只取第一条数据

代码如下: 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 tea...

以你上面的数据要求,用distinct 就可以实现,select distinct ID,TM,DATE from table,还有其他它分组等一些方式,你可以研究一下

WITH t AS (SELECT areaId FROM 表 GROUP BY areaId HAVING COUNT(areaId) > 1) SELECT * FROM 表 WHERE ReserverState = 'run' AND areaId IN(SELECT areaId FROM t) UNION ALL SELECT * FROM t WHERE areaId NOT IN (SELECT areaId FROM t)

select max(id) id,name from 表 group by name --如果表中还有其他字段要查出,那么 select * from 表 where id in (select max(id) from 表 group by name )

举个例子吧: A字段 B字段 C字段 D字段 001 0001 1 00001 002 0001 2 00001 这个时候 这两条记录只取一条 select * from tablename e where e.rowid in (select min(x.rowid) from tablename x where x.D字段 = e.D字段) 用min或者max会遍历

可用group by……having来实现。 可做如下测试: 1、创建表插入数据: create table test(id int,name varchar(10))insert into test values (1,'张三')insert into test values (2,'李四')insert into test values (3,'张三')insert into test va...

select 字段1,字段2,字段3,字段4 from A where 字段1 in (select min(字段1) from A t on A.字段 2=t.字段2) order by 字段1 即可以上功能,经过sql 2000/2005/2008完美测试

--判断一个字段重复就只显示一条,用distinct是不行的,可以用row_number()根据这个字段分组显示优先级,然后只取每个组的第一条select * from(select *,row_number() over(partition by 判断重复的字段名) as f_id from 表名) twhere f_id =1

可以这样写 select distinct id , name , max(yy) from A group by id , name 取id和name相同的最大yy,这样就只保留一条记录。

检索结果有漏掉?请楼主举例 我用联合查询的例子,楼主也可参考一下 SELECT a.Code, a.Name FROM 表A as a right join (select min(ID) as ID from 表A where Name like '%手机%' group by Code) as b on a.ID = b.ID order by len(a.Name) asc ...

网站地图

All rights reserved Powered by www.qmhc.net

copyright ©right 2010-2021。
www.qmhc.net内容来自网络,如有侵犯请联系客服。zhit325@qq.com