我遇到这样一种情况:用户更改了他的移动网络并订阅了我们的服务,但他们的手机号码是相同的。所以,对于他的旧订阅,我有他的旧移动网络,对于他的新订阅,我还有他的新移动网络记录。
例如
4474123456 subscribed gamesClub using O2 network on 2013-10-20
4474123456 subscribed musicClub using Orange network on 2013-12-05
因此,我想显示这些使用不同网络订阅我们服务的手机号码。
Database: MySQL
Table : MobileUsers
field:
userunique,mobileno, subscription,networkname, subscribedDate
查询我已尝试
SELECT mobileno,networkname,count(*) from MobileUsers
group by mobileno,networkname
SELECT mobileno,count(*) from MobileUsers group by mobileno,networkname
但没有一个结果令人满意。其中还包括拥有多个具有相同网络的订阅的mobileno。
谢谢你,
我认为您需要使用GROUP BY/HAVING
:
SELECT MobileNo
FROM MobileUsers
GROUP BY MobileNo
HAVING COUNT(DISTINCT networkname) > 1 -- MORE THAN ONE NETWORK
如果你需要取回这些手机号码的所有记录,你需要把上面的内容放在一个子查询中,然后加入它:
SELECT m.*
FROM MobileUsers m
INNER JOIN
( SELECT MobileNo
FROM MobileUsers
GROUP BY MobileNo
HAVING COUNT(DISTINCT networkname) > 1 -- MORE THAN ONE NETWORK
) Dupe
ON dupe.MobileNo = m.MobileNo
您需要使用一个子查询。
Select userunique, mobileno, subscription, networkname, subscribedDate
From MobileUsers as t1
Where exists
(
SELECT *
FROM MobileUsers as t2
WHERE t2.mobileno=t1.mobileno and t1.networkname<>t2.networkname
)
SELECT mobileno,count(*) as no
from MobileUsers
group by mobileno
minus
SELECT mobileno,count(*) as no
from MobileUsers
group by mobileno,networkname