www.1862.net > sql 分组去重问题求助

sql 分组去重问题求助

2种写法 1) SELECT t1.* FROM tab t1, (SELECT 字段3, 字段1 FROM tab GROUP BY 字段3, 字段1 HAVING COUNT(字段2) = 1) t2 WHERE t1.字段3 = t2.字段3 AND t1.字段1 = t2.字段1 或者 2) 这个效率高点 SELECT 字段1, MAX(字段2), 字段3 FROM ta...

可以这样 select * from table a where a.number = (select min(b.number) from table b where a.group = b.group)

你的distinct不能这么去重,要加上售货员, 比如存在一个例子 张三 111 李四 111 那么对于每个人来说都是一单 当然啦,如果你有业务逻辑,每个单号对应一个人,那么似乎可以。 SELECT 售货员,distinct(售货员,单号) as 单量 from 销售表然后对于...

delete xfrom A xwhere x.id > (select min(id) from A y where x.A1 = y.A1 and x.A2=y.A2); --方法一delete y from A y where y.id not in ( select min(id) id from A group by A1,A2 ) x --方法二 --第一步 select min(id) id,A1,A2 into #a...

SELECT A, B, C FROM TABLE_NAME AA WHERE ROWID = (SELECT MIN(ROWID) FROM TABLE_NAME BB WHERE BB.A = AA.A GROUP BY BB.A); 有点混乱,还不如说是某列重复时取第一条数据,你看看这是不是你要的

userId: user id url: url visited by the user SELECT userId, COUNT(DISTINCT url) FROM tab GROUP BY userId ORDER BY COUNT(DISTINCT url) DESC

select distinct username,password,password from users;试试这个,顺便问下,为什么会一个表有两个password字段

如果只是连续两次的话﹐我的办法是把它插入另外一个表﹐然后ID从2开始﹐再连接查询 DECLARE @TB1 TABLE(ID INT IDENTITY(1,1),NUM INT)DECLARE @TB2 TABLE(ID INT IDENTITY(2,1),NUM INT)INSERT INTO @TB1 VALUES(111)INSERT INTO @TB1 VALUES(1...

---你上面写的delete xfrom A xwhere x.id > (select min(id) from A y where x.A1 = y.A1 and x.A2=y.A2);--方法一delete y from A y where y.id not in( select min(id) id from A group by A1,A2) x--方法二 --第一步 select min(id) id,A1,A...

可以使用row_number()函数,该函数可以将相同的数据做归类,并附加一列,作为序数列,sql如下: select *,ROW_NUMBER() over (partition by address order by age desc) as rw from stu; 只要在该查询结果集外再嵌套一个取出rw=1的sql语句即可,...

网站地图

All rights reserved Powered by www.1862.net

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