The table I have is this:
| C1 | C2 | userid |
|---|---|---|
| 1 | 50 | 100 |
| 2 | 40 | 101 |
| 3 | 30 | 102 |
| 4 | 20 | 103 |
| 5 | 10 | 104 |
I need the userid and count() userid WHERE (Condition satisfied)
by input
userid IN (100,101,102,103,104,105)
The condition is :
C1 > (input userid's C1) and C2 < (input userid's C2)
I need it like this :
| userid | Count |
|---|---|
| 100 | 4 |
| 101 | 3 |
| 102 | 2 |
| 103 | 1 |
CodePudding user response：
Join and aggregate as the following:
SELECT D.userid, COUNT(*) cnt
FROM table_name T JOIN table_name D
ON T.C1 > D.C1 AND T.C2 < D.C2
WHERE D.userid IN (100,101,102,103,104,105)
GROUP BY D.userid
ORDER BY D.userid
See a demo.