How to find out a 1-to-many input
There is a dataset that suggests this is a 1-to-1 mapping. For example,
userId, deviceId aaa, 12345 bbb, 22398 ccc, 43284
However, there is a case where one deviceId has many userIds.
userId, deviceId ddd, 12094 eee, 12094 fff, 12094 ggg, 459834 hhh, 459834 iii, 459834 jjj, 459834
That SQL can only display those that are in many? Imagine there are many one-to-many records. I don't need a 1 to 1 record in the result. So, what I want to see as a result is something like
deviceId, _cnt 12094, 3 459834, 4
+3
KK
source
to share
3 answers
Hi you can use COUNT and then filter the ones that have COUNT greater than 1:
SELECT deviceId
, COUNT(DISTINCT userId) AS uct
, COUNT(userId) AS ct
FROM table
GROUP BY deviceId
HAVING ct > 1
AND uct > 1
0
RealCheeseLord
source
to share
It is recommended to use the column name instead of count (*). It will also improve performance in case of a large dataset.
SELECT deviceId,
COUNT(userId) AS user_count
FROM table
GROUP BY deviceId
HAVING COUNT(userId)> 1
+2
smau
source
to share
A simple account does this:
select deviceId, count(*) _cnt
from mytable
group by 1
having count(*) > 1
To list all entries that are not 1-1:
select userId, deviceId
from mytable
where deviceId in (
select deviceId
from mytable
group by 1
having count(*) > 1
)
or
select distinct a.userId, b.deviceId
from mytable a
join mytable b on b.deviceId = a.deviceId
and b.userId != a.deviceId
+1
Bohemian
source
to share