本文共 965 字,大约阅读时间需要 3 分钟。
数据库测试表dbo.Member
一,带有having条件的分组查询方法
(1)查询某一列重复记录
语句:
SELECT Name FROM dbo.Member t WHERE Name IN (SELECT Name FROM dbo.Member GROUP BY Name HAVING COUNT(Name)>1 ) ORDER BY t.Name
查询结果:
(2)查询某一列不重复的记录
语句:
SELECT * FROM dbo.Member WHERE ID IN (SELECT MIN(ID) FROM dbo.Member GROUP BY Name)
查询结果:
(3)清除某一列重复的数据
语句:
DELETE FROM dbo.Member WHERE ID NOT IN (SELECT MIN(ID) FROM dbo.Member GROUP BY Name)
执行结果:
解释:上面的例子只保存了各自Name的最小值。
二,DISTINCT 的用法
温馨提醒:不支持多列统计Oracle和DB2数据库也适用
利用distinct关键字返回唯一不同的值
(1)查询某一列不重复数据
语句:
SELECT DISTINCT Name FROM dbo.Member
结果集:
(2)DISTINCT 查询多列不重复(如果查询的列有任何一个不重复,则这条记录视为不重复)
语句:
SELECT DISTINCT Name,Uid FROM dbo.Member
查询结果
DISTINCT 用于统计语句
SELECT COUNT(DISTINCT(Name)) FROM dbo.Member
查找Original_ID和Match_ID这两列值重复的行
SELECT m.* FROM dbo.Match m,(SELECT Original_ID,Match_IDFROM dbo.Match GROUP BY Original_ID,Match_IDHAVING COUNT(1)>1) AS m1WHERE m.Original_ID=m1.Original_ID AND m.Match_ID=m1.Match_ID
转载地址:http://nmnci.baihongyu.com/