如何選擇不是最近且與上一個條目不同的行？我們通過背景關系欄位識別差異。
我的示例資料庫：
CREATE TABLE duel (
id int,
title varchar(255),
PRIMARY KEY (id)
);
CREATE TABLE try (
id int,
duel_id int,
context varchar(255),
recent tinyint(1),
PRIMARY KEY (id),
FOREIGN KEY (duel_id) REFERENCES duel(id)
);
INSERT INTO duel (id,title) VALUES (1,"1"),(2,"2"),(3,"3"),(4,"4");
INSERT INTO try (id,duel_id,context,recent) VALUES
(1,1,"a",0),(2,1,"a",0),(3,1,"a",1),(4,2,"a",0),(5,2,"b",0),
(6,2,"b",1),(7,3,"a",0),(8,3,"a",0),(9,3,"b",1),(10,4,"c",0),
(11,4,"a",0),(12,4,"c",1);
我想從try tableid 為 4、7、8 和 11 的行中檢索。
我嘗試了以下方法：
SELECT * FROM try
WHERE recent != 1 AND (SELECT context FROM try WHERE recent = 1) != context;
但我收到以下錯誤：
第 120 行的錯誤 1242 (21000)：子查詢回傳超過 1 行
我不知道如何處理它。也許除了子查詢之外還有其他解決方案？
uj5u.com熱心網友回復：
您的問題的解決方案：（對于 MySQL 5.7）
SELECT id,duel_id,context,recent
FROM
(
SELECT *,
CASE
WHEN @cntxt = context AND @did = duel_id AND recent = 0 THEN @cur
WHEN @cntxt = context AND @did = duel_id AND recent = 1 THEN (@cur := 1)
WHEN (@cntxt := context) IS NOT NULL AND (@did := duel_id) IS NOT NULL AND (@cur := recent) IS NOT NULL THEN recent
END as flag
FROM try, (SELECT @cur := 0, @cntxt := Null, @did := Null) r
ORDER BY duel_id, context,recent DESC
) as t
WHERE flag = 0
ORDER BY id;
db小提琴鏈接
您的問題的解決方案：（對于 MySQL 8.0 ）
WITH CT1 AS
(
SELECT *,
SUM(recent) OVER(PARTITION BY duel_id, context ORDER BY recent DESC) as rn
FROM try
)
SELECT id, duel_id,
context, recent
FROM CT1
WHERE rn = 0
ORDER BY id;
dbfiddle 鏈接
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/486199.html
上一篇：連接到其他計算機上的本地資料庫