Group only the adjacent rows
hi i have a table l开发者_Go百科ike this:
notifies
id,user_id
1,3
2,3
3,4
4,5
5,6
6,3
7,4
8,4
9,3
10,3
I have to create a query that group only the adjacent rows
So, the result of this example should be:
user_id
3
4
5
6
3
4
3
How can i do that? thanks
SELECT user_id
FROM notifies n
WHERE NOT
(
SELECT user_id
FROM notifies ni
WHERE ni.id < n.id
ORDER BY
id DESC
LIMIT 1
) <=> user_id
Select N.id, N.user_id
From notifies As N
Where Exists (
Select 1
From notifies As N2
Where N2.id = N.id + 1
And N2.user_id <> N.user_id
)
I think the best option is to make quite plain select, put result to some application and filter it there in more suitable (imperative) language. However, if You need, my pure MySQL versions. You have to order data by id
.
SELECT a.user_id
FROM notifies AS a
LEFT JOIN notifies AS c ON (
SELECT MIN(id) FROM notifies AS b WHERE b.id > a.id
) = c.id
WHERE a.user_id <> c.user_id OR c.user_id IS NULL
ORDER BY a.id
Second example:
SELECT c.user_id
FROM (
SELECT a.id, a.user_id, MIN(b.id) AS next
FROM notifies AS a
LEFT JOIN notifies AS b ON b.id > a.id
GROUP BY a.id, a.user_id
) AS c
LEFT JOIN notifies AS d ON d.id = c.next
WHERE c.user_id <> d.user_id OR c.next IS NULL
ORDER BY c.id
Try this:
- Select the notifies table, and place a row number using the ROW_NUMBER() command, ordered by id (call it id_no)
- Create a sub-select query also using row number (call it prev_id_no), but only selects the previous row (where prev_id_no = (id_no - 1))
- Nest the two select statements above under a SELECT * FROM (), so that you can manipulate the row_numbers
- Create a column which evaluates if #1 user_id is equal to #2 user_id. Use "CASE THEN 'YES ELSE 'NO' AS [DUPLICATE]"
- Nest the final resulting table from #3 and filter by DUPLICATE = 'NO'
SQL is this:
SELECT [user_id] FROM (
SELECT *,
CASE WHEN (
SELECT [user_id] FROM (
SELECT ROW_NUMBER() OVER(ORDER BY id) AS [prev_id_no],
[user_id]
FROM notifies
) FILTER_IN
WHERE FILTER_IN.prev_id_no = (FILTER_OUT.id_no - 1)
) = FILTER_OUT.[user_id] THEN 'YES' ELSE 'NO' END AS [DUPLICATE]
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY id) AS [id_no],
[user_id]
FROM notifies
) FILTER_OUT
) FILTER_FINAL
WHERE FILTER_FINAL.DUPLICATE = 'NO'
Result is:
user_id
3
4
5
6
3
4
3
精彩评论