select province,city,town,telphone,min(uername) from t group by province,city,town,telphone having count(1)>1 union all select province,city,town,min(telphone),uername from t group by province,city,town,uername having count(1)>1;
还有是不是应该用union 啊
思路:多次出现--得到的数据次数须大于1, 省+市+县 相同 --明显的group by子句 姓名 、手机任何一个相同即可---容易得知与上三个条件group hy
--SQL --先求出省+市+县相同与手机相同大于1的数据 SELECT ID, province,city,town,telphone FROM a HAVING COUNT(*)>1 GROUP BY province,city,town,telphone
--再求出省+市+县相同与姓名相同大于1的数据
SELECT ID, province,city,town,uername FROM a HAVING COUNT(*)>1 GROUP BY province,city,town,uername
--两者合并 SELECT Distinct b.Id, b.Province, b.City, b.Town, b.Telphone, b.Uername FROM (SELECT Id, Province, City, Town, Telphone FROM a HAVING COUNT(*) > 1 GROUP BY Province, City, Town, Telphone) b, (SELECT Id, Province, City, Town, Uername FROM a HAVING COUNT(*) > 1 GROUP BY Province, City, Town, Uername) c WHERE b.Id = c.Id
可以不用union吗?还有别的方法吗
SELECT PROVINCE, CITY, TOWN, TELPHONE, UERNAME, ID FROM (SELECT PROVINCE, CITY, TOWN, TELPHONE, UERNAME, ID, COUNT(TELPHONE) OVER(PARTITION BY PROVINCE, CITY, TOWN, UERNAME) CNT1, COUNT(UERNAME) OVER(PARTITION BY PROVINCE, CITY, TOWN, TELPHONE) CNT2 FROM T1) WHERE CNT1 + CNT2 > 2;
where条件 CNT1 + CNT2 > 2 么?
SELECT t1.ID, t1.province,t1.city,t1.town,t1.telphone,t1.name from tablename t1, tablename t2 where t1.id <> t2.id and t1.province = t2.province and t1.city= t2.cityand and t1.town= t2.town and ((t1.telphone = t2.telphone and t1.name <> t2.name) or (t1.telphone <> t2.telphone and t1.name = t2.name))
我解释下 --按照 PROVINCE, CITY, TOWN, UERNAME 分组求和本条记录 TELPHONE 相同的记录有多少条 COUNT(TELPHONE) OVER(PARTITION BY PROVINCE, CITY, TOWN, UERNAME) CNT1, --按照 PROVINCE, CITY, TOWN, TELPHONE 分组求和本条记录 UERNAME 相同的记录有多少条 COUNT(UERNAME) OVER(PARTITION BY PROVINCE, CITY, TOWN, TELPHONE) CNT2--这样求出来的cnt1和cnt2 要么=1 要么>=1 --如果cnt1=1 说明不存在一条记录保证和本条记录的 省+市+县 相同; 手机相同 --如果cnt2=1 说明不存在一条记录保证和本条记录的 省+市+县 相同; 姓名相同 --所以只要保证 cnt1+cnt2 > 2 那么就能保证 ----省+市+县 相同; 姓名 、手机任何一个相同所以sql就这么写了,O(∩_∩)O~
count(*)在执行的时候会被解释成表中的所有列, 而count(1)是指定了1个计算列1 count(1) 比 count(*)快select ...结果集1 union all select ...结果集2union all 对比 union union会 从结果集1中驱除 对结果集2重复的记录 因为已知结果集1与结果集2,不可能重复, 所以用union all跳高效率(告诉数据库结果集1与结果集2别比了,全列出来吧)
select province,city,town,telphone,min(uername) from t
group by province,city,town,telphone
having count(1)>1
union all
select province,city,town,min(telphone),uername from t
group by province,city,town,uername
having count(1)>1;
思路:多次出现--得到的数据次数须大于1,
省+市+县 相同 --明显的group by子句
姓名 、手机任何一个相同即可---容易得知与上三个条件group hy
--SQL
--先求出省+市+县相同与手机相同大于1的数据
SELECT ID, province,city,town,telphone
FROM a
HAVING COUNT(*)>1
GROUP BY province,city,town,telphone
--再求出省+市+县相同与姓名相同大于1的数据
SELECT ID, province,city,town,uername
FROM a
HAVING COUNT(*)>1
GROUP BY province,city,town,uername
--两者合并
SELECT Distinct b.Id, b.Province, b.City, b.Town, b.Telphone, b.Uername
FROM (SELECT Id, Province, City, Town, Telphone
FROM a
HAVING COUNT(*) > 1
GROUP BY Province, City, Town, Telphone) b,
(SELECT Id, Province, City, Town, Uername
FROM a
HAVING COUNT(*) > 1
GROUP BY Province, City, Town, Uername) c
WHERE b.Id = c.Id
SELECT PROVINCE, CITY, TOWN, TELPHONE, UERNAME, ID
FROM (SELECT PROVINCE,
CITY,
TOWN,
TELPHONE,
UERNAME,
ID,
COUNT(TELPHONE) OVER(PARTITION BY PROVINCE, CITY, TOWN, UERNAME) CNT1,
COUNT(UERNAME) OVER(PARTITION BY PROVINCE, CITY, TOWN, TELPHONE) CNT2
FROM T1)
WHERE CNT1 + CNT2 > 2;
from tablename t1, tablename t2
where t1.id <> t2.id and t1.province = t2.province
and t1.city= t2.cityand and t1.town= t2.town
and ((t1.telphone = t2.telphone and t1.name <> t2.name)
or (t1.telphone <> t2.telphone and t1.name = t2.name))
--按照 PROVINCE, CITY, TOWN, UERNAME 分组求和本条记录 TELPHONE 相同的记录有多少条
COUNT(TELPHONE) OVER(PARTITION BY PROVINCE, CITY, TOWN, UERNAME) CNT1,
--按照 PROVINCE, CITY, TOWN, TELPHONE 分组求和本条记录 UERNAME 相同的记录有多少条
COUNT(UERNAME) OVER(PARTITION BY PROVINCE, CITY, TOWN, TELPHONE) CNT2--这样求出来的cnt1和cnt2 要么=1 要么>=1
--如果cnt1=1 说明不存在一条记录保证和本条记录的 省+市+县 相同; 手机相同
--如果cnt2=1 说明不存在一条记录保证和本条记录的 省+市+县 相同; 姓名相同
--所以只要保证 cnt1+cnt2 > 2 那么就能保证
----省+市+县 相同; 姓名 、手机任何一个相同所以sql就这么写了,O(∩_∩)O~
count(*)在执行的时候会被解释成表中的所有列,
而count(1)是指定了1个计算列1
count(1) 比 count(*)快select ...结果集1
union all
select ...结果集2union all 对比 union
union会 从结果集1中驱除 对结果集2重复的记录
因为已知结果集1与结果集2,不可能重复,
所以用union all跳高效率(告诉数据库结果集1与结果集2别比了,全列出来吧)