我有一個loop_msg用msg_idand呼叫的表content。我有第二張桌子loop_msg_status，用channeland呼叫msg_id。這用于在不同渠道中以回圈方式發布訊息，因此我需要跟蹤msg_id每個channel.
SELECT
a.msg_id,
b.content,
b.rank,
b.rank 1,
c.rank,
c.content as next_content
FROM
loop_msg_status as a
LEFT JOIN (
SELECT
*,
RANK() OVER (
ORDER BY
msg_id ASC
) as rank
FROM
loop_msg
) b ON a.msg_id = b.msg_id
LEFT JOIN (
SELECT
*,
RANK() OVER (
ORDER BY
msg_id ASC
) as rank
FROM
loop_msg
) c ON b.rank 1 = c.rank
通過這個查詢，我可以msg_id從content每個channel. 我也從 table得到那個msg_id。如果有意義的話，我也會得到它和得到的。它有效。但是，如果是最高的，則不存在，我得到一個. 在這種情況下，我想從最低的is 中獲取它的內容。我應該添加一個，如果是，在哪里？還是有更好的方法來做到這一點？rankloop_msgrank 1rank 1contentrankrank 1NULL next_contentSELECTrankloop_msg1next_contentIF()
SELECT version();
> 10.5.13-MariaDB
完整的 SQL 示例：
CREATE TABLE `loop_msg` (
`msg_id` int(11) NOT NULL,
`content` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `loop_msg` (`msg_id`, `content`) VALUES
(2, 'Content 2'),
(3, 'Content 3'),
(4, 'Content 4'),
(6, 'Content 6'),
(7, 'Content 7'),
(8, 'Content 8');
CREATE TABLE `loop_msg_status` (
`channel` bigint(20) NOT NULL,
`msg_id` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `loop_msg_status` (`channel`, `msg_id`) VALUES
(316757642527768577, 4),
(384071823261696010, 6),
(939746456632438804, 8);
ALTER TABLE `loop_msg`
ADD PRIMARY KEY (`msg_id`);
ALTER TABLE `loop_msg_status`
ADD PRIMARY KEY (`channel`);
ALTER TABLE `loop_msg`
MODIFY `msg_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
COMMIT;
預期結果
channel | nextContent
--------------------------------
316757642527768577 | Content 6
384071823261696010 | Content 7
939746456632438804 | Content 2
uj5u.com熱心網友回復：
帶有問題中給出的架構（和預期結果）的最終 SQL：
最后的小提琴
WITH cte AS (
SELECT loop_msg.msg_id
, channel
, COALESCE(
LEAD(content) OVER w1
, FIRST_VALUE(content) OVER w1
) AS content_next
FROM loop_msg
LEFT JOIN loop_msg_status
ON loop_msg.msg_id = loop_msg_status.msg_id
WINDOW w1 AS (ORDER BY loop_msg.msg_id)
)
SELECT channel, content_next
FROM cte
WHERE channel IS NOT NULL
;
結果：
-------------------- --------------
| channel | content_next |
-------------------- --------------
| 316757642527768577 | Content 6 |
| 384071823261696010 | Content 7 |
| 939746456632438804 | Content 2 |
-------------------- --------------
要同時查看當前和下一個 msg_id，以下是調整后的 SQL：
調整后的小提琴
WITH cte AS (
SELECT loop_msg.msg_id
, channel
, ROW_NUMBER() OVER w1 as rankx
, COALESCE(
LEAD(loop_msg.msg_id) OVER w1
, FIRST_VALUE(loop_msg.msg_id) OVER w1
) AS msgid_next
, COALESCE(
LEAD(content) OVER w1
, FIRST_VALUE(content) OVER w1
) AS content_next
FROM loop_msg
LEFT JOIN loop_msg_status
ON loop_msg.msg_id = loop_msg_status.msg_id
WINDOW w1 AS (ORDER BY loop_msg.msg_id)
)
SELECT channel, content_next, msg_id, msgid_next
FROM cte
WHERE channel IS NOT NULL
;
結果：
-------------------- -------------- -------- ------------
| channel | content_next | msg_id | msgid_next |
-------------------- -------------- -------- ------------
| 316757642527768577 | Content 6 | 4 | 6 |
| 384071823261696010 | Content 7 | 6 | 7 |
| 939746456632438804 | Content 2 | 8 | 2 |
-------------------- -------------- -------- ------------
更多詳情：
你可以試試這樣的。在這種情況下，我們可以使用 ROW_NUMBER 代替 RANK，因為 msg_id 是唯一的，沒有兩條訊息將具有相同的 msg_id 并具有相同的 RANK。如果您愿意，請隨意替換為 RANK。
小提琴
稍后，我們可以按通道進行磁區，分別為每個通道執行此操作。您的問題并不完全清楚您想如何使用頻道。
SELECT *
, ROW_NUMBER() OVER (ORDER BY msg_id ASC) as rankx
, COALESCE(
LEAD(msg_id) OVER (ORDER BY msg_id ASC)
, FIRST_VALUE(msg_id) OVER (ORDER BY msg_id ASC)
) AS msgid_next
, COALESCE(
LEAD(content) OVER (ORDER BY msg_id ASC)
, FIRST_VALUE(content) OVER (ORDER BY msg_id ASC)
) AS content_next
FROM loop_msg
;
結果：
-------- --------- ------- ------------ --------------
| msg_id | content | rankx | msgid_next | content_next |
-------- --------- ------- ------------ --------------
| 1 | c1 | 1 | 2 | c2 |
| 2 | c2 | 2 | 3 | c3 |
| 3 | c3 | 3 | 4 | c4 |
| 4 | c4 | 4 | 5 | c5 |
| 5 | c5 | 5 | 6 | c6 |
| 6 | c6 | 6 | 7 | c7 |
| 7 | c7 | 7 | 1 | c1 |
-------- --------- ------- ------------ --------------
設定：
CREATE TABLE loop_msg (
msg_id int auto_increment primary key
, content varchar(20)
);
INSERT INTO loop_msg (content) VALUES
('c1'), ('c2'), ('c3'), ('c4'), ('c5'), ('c6'), ('c7')
;
測驗演員#2，每個通道的處理：
CREATE TABLE loop_msg (
msg_id int auto_increment primary key
, chan varchar(20)
, content varchar(20)
);
INSERT INTO loop_msg (content, chan) VALUES
('c1', 'chan1')
, ('c2', 'chan1')
, ('c3', 'chan1')
, ('c4', 'chan1')
, ('c5', 'chan1')
, ('c6', 'chan1')
, ('c7', 'chan1')
, ('d2', 'chan2')
, ('d3', 'chan2')
, ('d4', 'chan2')
, ('d5', 'chan2')
, ('d6', 'chan2')
, ('d7', 'chan2')
, ('d8', 'chan2')
;
SELECT *
, ROW_NUMBER() OVER (PARTITION BY chan ORDER BY msg_id ASC) as rankx
, COALESCE(
LEAD(msg_id) OVER (PARTITION BY chan ORDER BY msg_id)
, FIRST_VALUE(msg_id) OVER (PARTITION BY chan ORDER BY msg_id)
) AS msgid_next
, COALESCE(
LEAD(content) OVER (PARTITION BY chan ORDER BY msg_id)
, FIRST_VALUE(content) OVER (PARTITION BY chan ORDER BY msg_id)
) AS content_next
FROM loop_msg
;
結果：
-------- ------- --------- ------- ------------ --------------
| msg_id | chan | content | rankx | msgid_next | content_next |
-------- ------- --------- ------- ------------ --------------
| 1 | chan1 | c1 | 1 | 2 | c2 |
| 2 | chan1 | c2 | 2 | 3 | c3 |
| 3 | chan1 | c3 | 3 | 4 | c4 |
| 4 | chan1 | c4 | 4 | 5 | c5 |
| 5 | chan1 | c5 | 5 | 6 | c6 |
| 6 | chan1 | c6 | 6 | 7 | c7 |
| 7 | chan1 | c7 | 7 | 1 | c1 |
| 8 | chan2 | d2 | 1 | 9 | d3 |
| 9 | chan2 | d3 | 2 | 10 | d4 |
| 10 | chan2 | d4 | 3 | 11 | d5 |
| 11 | chan2 | d5 | 4 | 12 | d6 |
| 12 | chan2 | d6 | 5 | 13 | d7 |
| 13 | chan2 | d7 | 6 | 14 | d8 |
| 14 | chan2 | d8 | 7 | 8 | d2 |
-------- ------- --------- ------- ------------ --------------
最后：
We can also define a window clause to avoid rewriting the specification each time:
SELECT *
, ROW_NUMBER() OVER w1 as rankx
, COALESCE(
LEAD(msg_id) OVER w1
, FIRST_VALUE(msg_id) OVER w1
) AS msgid_next
, COALESCE(
LEAD(content) OVER w1
, FIRST_VALUE(content) OVER w1
) AS content_next
FROM loop_msg
WINDOW w1 AS (PARTITION BY chan ORDER BY msg_id)
;
Result:
-------- ------- --------- ------- ------------ --------------
| msg_id | chan | content | rankx | msgid_next | content_next |
-------- ------- --------- ------- ------------ --------------
| 1 | chan1 | c1 | 1 | 2 | c2 |
| 2 | chan1 | c2 | 2 | 3 | c3 |
| 3 | chan1 | c3 | 3 | 4 | c4 |
| 4 | chan1 | c4 | 4 | 5 | c5 |
| 5 | chan1 | c5 | 5 | 6 | c6 |
| 6 | chan1 | c6 | 6 | 7 | c7 |
| 7 | chan1 | c7 | 7 | 1 | c1 |
| 8 | chan2 | d2 | 1 | 9 | d3 |
| 9 | chan2 | d3 | 2 | 10 | d4 |
| 10 | chan2 | d4 | 3 | 11 | d5 |
| 11 | chan2 | d5 | 4 | 12 | d6 |
| 12 | chan2 | d6 | 5 | 13 | d7 |
| 13 | chan2 | d7 | 6 | 14 | d8 |
| 14 | chan2 | d8 | 7 | 8 | d2 |
-------- ------- --------- ------- ------------ --------------
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/427768.html