我有一个查询,它列出了我们网站上"页面"中的ID。
SELECT mdl_page.id
FROM mdl_page, mdl_log, mdl_user
WHERE mdl_log.module = "page"
AND mdl_log.action = "view"
AND mdl_user.id = mdl_log.userid
AND mdl_log.info = mdl_page.id
AND mdl_log.course = 178
结果很简单:
| ID |
|-----|
| 3 |
| 4 |
| 7 |
| 11 |
注意计数的跳跃。我正试图得到这样的东西:
| ID | NEXT ID |
|-----|---------|
| 3 | 4 |
| 4 | 7 |
| 7 | 11 |
| 11 | 12 |
有人能为我指出正确的方向吗?
更新
一个转折点是,我必须运行查询的系统(而不是我自己的系统)只允许以"SELECT"开头的查询。
我可以想出两种使用共同相关子查询的方法,在子查询中比较主查询的值,并以升序对其进行排序,并将结果限制为一个
SELECT
p.id ,
(SELECT
p1.id
FROM mdl_page p1
JOIN mdl_log l1 ON (l1.info = p1.id)
JOIN mdl_user u1 ON (u1.id = l1.userid)
WHERE l1.module = "page"
AND l1.action = "view"
AND l1.course = 178
AND p1.id > p.id
ORDER BY p1.id ASC LIMIT 1) NEXT_ID
FROM mdl_page p
JOIN mdl_log l ON (l.info = p.id)
JOIN mdl_user u ON (u.id = l.userid)
WHERE l.module = "page" AND l.action = "view" AND l.course = 178
ORDER BY p.id
并且使用秩查询,在秩查询中,我加入了具有小于条件ON (t.
id < t1.id)
的同一查询,因此它将导致多行,如(3,4)、(3,7)、(3,11),因此我需要选择3,4的第一个组合,为此我已使用秩查询将秩赋予属于同一组的项目,在父项中,我只是限制结果集显示每组的第一对
SELECT t3.id,t3.NEXT_ID FROM (
SELECT t.id id, t1.id NEXT_ID ,
@r:= CASE WHEN @g = t.id THEN @r +1 ELSE 1 END rownum,
@g:= t.id
FROM
(SELECT
p.id
FROM
mdl_page p
JOIN mdl_log l ON (l.info = p.id)
JOIN mdl_user u ON (u.id = l.userid)
WHERE l.module = "page"
AND l.action = "view"
AND l.course = 178
ORDER BY p.id
) t
LEFT JOIN
(SELECT
p.id
FROM
mdl_page p
JOIN mdl_log l ON (l.info = p.id)
JOIN mdl_user u ON (u.id = l.userid)
WHERE l.module = "page"
AND l.action = "view"
AND l.course = 178
ORDER BY p.id ) t1 ON (t.`id` < t1.id)
CROSS JOIN (SELECT @g:=0,@r:=0) t2
ORDER BY t.`ID` , t1.ID
) t3
WHERE t3.rownum = 1
resutset如果不再存在id大于11的记录,或者换句话说,最后一条记录在下一个id列中将为null,则11将为null
ID NEXT_ID
3 4
4 7
7 11
11 NULL
也许您应该创建一个临时表,它与您正在运行的查询几乎相同,并擦除第一行?
然后运行查询并将其与临时表连接?