Выберите 10 строк, но только макс 2 за категорию ⇐ MySql

[Anonymous]

У меня есть приведенный ниже запрос, который я хотел бы изменить, чтобы выбрать максимум из 2 строк на категорию (c.catid) < /p>
SELECT d.ID, c.catID
FROM decks AS d
LEFT JOIN deck_extra_cats AS c ON d.ID = c.deckID
WHERE d.status = 'made'
ORDER BY RAND()
LIMIT 10;

Я знаю, что могу изменить запрос и вручную устанавливать категории, но не существует определенного количества категорий, и мне нужно будет изменять каждый раз, когда добавляется новый. /> Я бы хотел, чтобы у каждой категории были возвращены только 2 ряда, так что один запуск может быть (DeckId, Catid) < /p>
('ALWAYSANDFOREVER', 'THEORIGINALS'),
('CUREBERRY', 'PRETTYCURE'),
('ENDWALKERTRAILERA', 'FINALFANTASY14'),
('CREATINGAMONSTER', 'THEORIGINALS'),
('ENDWALKERTRAILERB', 'FINALFANTASY14'),
('HESGONE', 'THEVAMPIREDIARIES'),
('KARI', 'DIGIMON'),
('KIZUNATRAILERB', 'DIGIMON'),
('LEGACIESLOGO', 'LEGACIES'),
('VINCENTVALENTINE', 'FINALFANTASY7'),
('WHENISAYFRIEND', 'THEVAMPIREDIARIES'),
< /code>
Таблица колод < /p>
CREATE TABLE `decks` (
`ID` varchar(50) NOT NULL,
`name` varchar(50) NOT NULL,
`description` text NOT NULL,
`episode` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`episodeShort` varchar(7) NOT NULL,
`type` varchar(25) NOT NULL,
`worth` int NOT NULL DEFAULT '1',
`cardBlank` varchar(50) NOT NULL,
`status` enum('released','made','unavailable') NOT NULL DEFAULT 'unavailable',
`dateAdded` date NOT NULL,
`dateMade` date NOT NULL,
`dateReleased` date NOT NULL,
`albumID` int NOT NULL,
PRIMARY KEY (`ID`),
KEY `cardBlank` (`cardBlank`),
KEY `decks_ibfk_2` (`type`),
KEY `dateReleased` (`dateReleased`),
KEY `dateAdded` (`dateAdded`),
KEY `episodeShort` (`episodeShort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `decks` (`ID`, `name`, `description`, `episode`, `episodeShort`, `type`, `worth`, `cardBlank`, `status`, `dateAdded`, `dateMade`, `dateReleased`, `albumID`) VALUES
('ALWAYSANDFOREVER', 'Always And Forever', 'Hope\'s painting from the final episode', '', '', 'PUZZLE', 1, 'alwaysandforever00', 'made', '2025-04-24', '2025-04-24', '0000-00-00', 1663),
('BEGGARSCANTBECHOOSERS', 'Beggars Can\'t Be Choosers', 'The Mikaelson siblings wake in the Chambre de Chasse', 'Season 3 - Episode 22', 'S3 E22', 'SCENE', 1, 'beggarscantbechoosers00', 'made', '2025-04-05', '2025-04-05', '0000-00-00', 2059),
('CREATINGAMONSTER', 'Creating A Monster', 'Marcel and Vincent wipe Elijah\'s memories', 'Season 4 - Episode 13', 'S4 E13', 'SCENE', 1, 'creatingamonster00', 'made', '2025-01-18', '2025-01-25', '0000-00-00', 1668),
('CUREBERRY', 'Cure Berry', '', '', '', 'CHARACTER', 1, 'cureberry00', 'made', '2024-12-05', '2025-04-20', '0000-00-00', 2077),
('ENDWALKERTRAILERA', 'Endwalker Trailer A', '', '', '', 'SPECIAL', 2, 'endwalkertrailera00', 'made', '2024-12-04', '2025-06-14', '0000-00-00', 1632),
('ENDWALKERTRAILERB', 'Endwalker Trailer B', '', '', '', 'SPECIAL', 2, 'endwalkertrailerb00', 'made', '2024-12-04', '2025-06-14', '0000-00-00', 1633),
('EXERCISEEXTREMECAUTION', 'Exercise Extreme Caution', '', 'Season 1 - Episode 21', 'S1 E21', 'SCENE', 1, 'exerciseextremecaution00', 'made', '2025-01-04', '2025-01-04', '0000-00-00', 1671),
('FINISHIT', 'Finish It', 'Elijah & Freya sees the complete prophecy', 'Season 3 - Episode 21', 'S3 E21', 'SCENE', 1, 'finishit00', 'made', '2025-04-29', '2025-05-01', '0000-00-00', 2099),
('HAYLEYHALLUCINATION', 'Hayley Hallucination', 'Deleted scene - Elijah hallucinates finding Hayley', 'Season 1 - Episode 22', 'S1 E22', 'SCENE', 1, 'hayleyhallucination00', 'made', '2025-01-17', '2025-01-25', '0000-00-00', 1676),
('HESGONE', 'He\'s Gone', 'Rebekah tells Elijah Klaus is dead', 'Season 3 - Episode 22', 'S3 E22', 'SCENE', 1, 'hesgone00', 'made', '2025-05-17', '2025-06-07', '0000-00-00', 2114),
('HESNOTGONE', 'He\'s Not Gone', 'Freya tries to locate Elijah\'s soul in the pendant', 'Season 4 - Episode 9', 'S4 E09', 'SCENE', 1, 'hesnotgone00', 'made', '2025-05-02', '2025-05-03', '0000-00-00', 2107),
('HOPEFORYOU', 'Hope For You', 'Elijah tries to protect Rebekah from Klaus who stabs him with Papa Tunde\'s Blade', 'Season 1 - Episode 16', 'S1 E16', 'SCENE', 1, 'hopeforyou00', 'made', '2025-04-20', '2025-04-20', '0000-00-00', 2080),
('ICANNOTFORGIVETHIS', 'I Can Not Forgive This', 'Elijah can’t bring himself to kill Aya', 'Season 3 - Episode 14', 'S3 E14', 'SCENE', 1, 'icannotforgivethis00', 'made', '2025-06-12', '2025-06-13', '0000-00-00', 2125),
('IDDOANYTHING', 'I\'d Do Anything', '', 'Season 2 - Episode 6', 'S2 E06', 'SCENE', 1, 'iddoanything00', 'made', '2024-12-28', '2024-12-28', '0000-00-00', 1679),
('ILOVEMYFAMILY', 'I Love My Family', 'Klaus and Rebekah talk', 'Season 1 - Episode 16', 'S1 E16', 'SCENE', 1, 'ilovemyfamily00', 'made', '2025-05-08', '2025-05-09', '0000-00-00', 2112),
('IMSORRY', 'I’m Sorry', 'Elijah apologises to Klaus about the baby', 'Season 1 - Episode 9', 'S1 E09', 'SCENE', 1, 'imsorry00', 'made', '2025-05-22', '2025-06-11', '0000-00-00', 2117),
('IMWARNINGYOU', 'I\'m Warning You', 'Elijah tells Klaus that if he wants something, he\'ll take it', 'Season 1 - Episode 18', 'S1 E18', 'SCENE', 1, 'imwarningyou00', 'made', '2025-06-13', '2025-06-13', '0000-00-00', 2147),
('ITLLDESTROYHIM', 'It\'ll Destroy Him', 'Freya tells Klaus Marcel and Vincent are going to restore Elijah\'s memories', 'Season 5 - Episode 7', 'S5 E07', 'SCENE', 1, 'itlldestroyhim00', 'made', '2025-01-11', '2025-01-25', '0000-00-00', 1680),
('JEANSSHORTSANDFLIPFLOPS', 'Jeans, Shorts and Flip Flops', 'Hayley and Elijah discuss living out in the bayou', 'Season 4 - Episode 5', 'S4 E05', 'SCENE', 1, 'jeansshortsandflipflops00', 'made', '2025-06-13', '2025-06-13', '0000-00-00', 2145),
('KARI', 'Kari', '', '', '', 'CHARACTER', 1, 'kari00', 'made', '2024-12-04', '2025-06-14', '0000-00-00', 1470),
('KINDOFABIGDEAL', 'Kind Of A Big Deal', 'Elijah and Hayley attend the Strix event', 'Season 3 - Episode 4', 'S3 E04', 'SCENE', 1, 'kindofabigdeal00', 'made', '2025-06-12', '2025-06-13', '0000-00-00', 2127),
('KIZUNATRAILERA', 'KIZUNA Trailer A', '', '', '', 'SPECIAL', 2, 'kizunatrailera00', 'made', '2024-12-04', '2025-06-14', '0000-00-00', 1484),
('KIZUNATRAILERB', 'KIZUNA Trailer B', '', '', '', 'SPECIAL', 2, 'kizunatrailerb00', 'made', '2024-12-04', '2025-06-14', '0000-00-00', 1484),
('LEGACIESLOGO', 'Legacies Logo', '', '', '', 'PUZZLE', 1, 'legacieslogo00', 'made', '2025-04-21', '2025-04-22', '0000-00-00', 2097),
('NOTGOINGTOKILLYOU', 'Not Going To Kill You', 'Klaus refuses to allow Elijah to take the Hollow', 'Season 5 - Episode 12', 'S5 E12', 'SCENE', 1, 'notgoingtokillyou00', 'made', '2025-05-17', '2025-06-07', '0000-00-00', 2113),
('NOTTHISTHINGSPUPPET', 'Not This Things Puppet', 'Klaus faces Marcel', 'Season 4 - Episode 5', 'S4 E05', 'SCENE', 1, 'notthisthingspuppet00', 'made', '2025-06-12', '2025-06-13', '0000-00-00', 2129),
('NOWIHAVETHIS', 'Now I Have This', 'Rebekah reveals she has the white oak stake', 'Season 1 - Episode 16', 'S1 E16', 'SCENE', 1, 'nowihavethis00', 'made', '2025-05-08', '2025-05-09', '0000-00-00', 2108),
('ONEWEAKNESS', 'One Weakness', 'Klaus daggers Elijah', 'Season 1 - Episode 1', 'S1 E01', 'SCENE', 1, 'oneweakness00', 'made', '2025-01-15', '2025-01-25', '0000-00-00', 1684),
('REMEMBERYOURFAMILY', 'Remember Your Family', 'Marcel and Vincent restore Elijah\'s memories', 'Season 5 - Episode 7', 'S5 E07', 'SCENE', 1, 'rememberyourfamily00', 'made', '2025-01-18', '2025-01-25', '0000-00-00', 1686),
('RUNFARANDFAST', 'Run Far and Fast', 'Elijah stabs Klaus with Papa Tunde\'s Blade, protecting Rebekah', 'Season 1 - Episode 14', 'S1 E14', 'SCENE', 1, 'runfarandfast00', 'made', '2025-04-19', '2025-04-19', '0000-00-00', 2074),
('SHAREDMEMORY', 'Shared Memory', 'Elijah & Rebekah talk while Elijah is daggered', 'Season 1 - Episode 4', 'S1 E04', 'SCENE', 1, 'sharedmemory00', 'made', '2025-03-20', '2025-03-20', '0000-00-00', 2029),
('SHESDOINGOK', 'She\'s Doing OK', 'Elijah and Eve converse while watching Hayley', 'Season 1 - Episode 18', 'S1 E18', 'SCENE', 1, 'shesdoingok00', 'made', '2025-06-13', '2025-06-13', '0000-00-00', 2148),
('SURVIVALDEMANDSSACRIFICE', 'Survival Demands Sacrifice', 'Elijah and Hayley talk', 'Season 4 - Episode 8', 'S4 E08', 'SCENE', 1, 'survivaldemandssacrifice00', 'made', '2025-04-18', '2025-04-18', '0000-00-00', 2070),
('THELITTLEONESYOUSEEK', 'The Little Ones You Seek', 'Elijah & Oliver vs Werewolves', 'Season 2 - Episode 4', 'S2 E04', 'SCENE', 1, 'thelittleonesyouseek00', 'made', '2025-05-08', '2025-05-09', '0000-00-00', 2110),
('THOSEIMMORTALTYRANTS', 'Those Immortal Tyrants', 'The Hollow appears to Marcel as Elijah and Klaus', 'Season 4 - Episode 5', 'S4 E05', 'SCENE', 1, 'thoseimmortaltyrants00', 'made', '2025-06-13', '2025-06-13', '0000-00-00', 2146),
('TRUSTMENOW', 'Trust Me Now', 'Klaus faces Marcel', 'Season 4 - Episode 5', 'S4 E05', 'SCENE', 1, 'trustmenow00', 'made', '2025-06-12', '2025-06-13', '0000-00-00', 2128),
('UNRELENTINGTRAGEDY', 'Unrelenting Tragedy', 'The Hollow appears to Marcel as Elijah', 'Season 4 - Episode 5', 'S4 E05', 'SCENE', 1, 'unrelentingtragedy00', 'made', '2025-06-13', '2025-06-13', '0000-00-00', 2144),
('VAMPIREBOOKCLUB', 'Vampire Book Club', 'Elijah and Klaus reading', 'Season 1 - Episode 6', 'S1 E06', 'SCENE', 1, 'vampirebookclub00', 'made', '2025-06-12', '2025-06-13', '0000-00-00', 2130),
('VINCENTVALENTINE', 'Vincent Valentine', '', '', '', 'CHARACTER', 1, 'vincentvalentine00', 'made', '2024-12-04', '2025-06-14', '0000-00-00', 1497),
('WHATAREYOUPEOPLE', 'What Are You People', 'Declan witnesses Hopes attack on Elijah', 'Season 5 - Episode 9', 'S5 E09', 'SCENE', 1, 'whatareyoupeople00', 'made', '2025-01-27', '2025-01-31', '0000-00-00', 1728),
('WHATEVERTROUBLE', 'Whatever Trouble', 'Hayley comes to Elijah\'s aid, then finds herself in trouble', 'Season 3 - Episode 8', 'S3 E08', 'SCENE', 1, 'whatevertrouble00', 'made', '2025-02-16', '2025-02-16', '0000-00-00', 1992),
('WHATYOUREALLYARE', 'What You Really Are', 'Davina beats Klaus and Elijah, Rebekah stakes Marcel', 'Season 1 - Episode 10', 'S1 E10', 'SCENE', 1, 'whatyoureallyare00', 'made', '2025-05-21', '2025-06-11', '0000-00-00', 2116),
('WHENISAYFRIEND', 'When I Say \"Friend\"', 'Katherine reveals she was meeting with Elijah', 'Season 4 - Episode 18', 'S4 E18', 'SCENE', 1, 'whenisayfriend00', 'made', '2025-04-20', '2025-04-20', '0000-00-00', 2082),
('WHOSEM', 'Who\'s Em?', 'Rekebkah and Elena question Katherine on who she\'s meeting with', 'Season 4 - Episode 18', 'S4 E18', 'SCENE', 1, 'whosem00', 'made', '2025-04-20', '2025-04-20', '0000-00-00', 2081),
('YOUCANDOBETTER', 'You Can Do Better', 'Lucien Fights Elijah and Finn before biting Finn', 'Season 3 - Episode 17', 'S3 E17', 'SCENE', 1, 'youcandobetter00', 'made', '2025-06-12', '2025-06-13', '0000-00-00', 2126),
('YOUHEARDOFME', 'You Heard Of Me?', 'Elijah introduces himself to Sophie', 'Season 1 - Episode 1', 'S1 E01', 'SCENE', 1, 'youheardofme00', 'made', '2025-03-02', '2025-03-02', '0000-00-00', 2012),
('YOUREKILLINGTHEMB', 'You\'re Killing Them B', 'Dahlia tries to kill Elijah, Klaus, Rebekah and Freya', 'Season 2 - Episode 22', 'S2 E22', 'SCENE', 1, 'yourekillingthemb00', 'made', '2025-01-04', '2025-01-04', '0000-00-00', 1699),
('YOUSEEMTROUBLED', 'You Seem Troubled', 'Esther Creates an illusion of Elijah to trick Klaus', 'Season 2- Episode 6', 'S2 E06', 'SCENE', 1, 'youseemtroubled00', 'made', '2025-04-18', '2025-04-18', '0000-00-00', 2073),
('YOUWILLPAYFORTHIS', 'You Will Pay For This', 'Klaus threatens Elijah after he removes Papa Tunde\'s Blade', 'Season 1 - Episode 15', 'S1 E15', 'SCENE', 1, 'youwillpayforthis00', 'made', '2025-04-29', '2025-05-01', '0000-00-00', 2101);
< /code>
и таблица Deck_extra_cats < /p>
CREATE TABLE `deck_extra_cats` (
`deckID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`catID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`deckID`,`catID`),
KEY `deckID` (`deckID`),
KEY `catID` (`catID`),
CONSTRAINT `deck_extra_cats_ibfk_1` FOREIGN KEY (`deckID`) REFERENCES `decks` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci;

INSERT INTO deck_extra_cats (`deckID`, `catID`) VALUES
('ALWAYSANDFOREVER', 'THEORIGINALS'),
('BEGGARSCANTBECHOOSERS', 'THEORIGINALS'),
('CREATINGAMONSTER', 'THEORIGINALS'),
('CUREBERRY', 'PRETTYCURE'),
('ENDWALKERTRAILERA', 'FINALFANTASY14'),
('ENDWALKERTRAILERB', 'FINALFANTASY14'),
('EXERCISEEXTREMECAUTION', 'THEORIGINALS'),
('FINISHIT', 'THEORIGINALS'),
('HAYLEYHALLUCINATION', 'THEORIGINALS'),
('HESGONE', 'THEVAMPIREDIARIES'),
('HESNOTGONE', 'THEORIGINALS'),
('HOPEFORYOU', 'THEORIGINALS'),
('ICANNOTFORGIVETHIS', 'THEORIGINALS'),
('IDDOANYTHING', 'THEORIGINALS'),
('ILOVEMYFAMILY', 'THEORIGINALS'),
('IMSORRY', 'THEORIGINALS'),
('IMWARNINGYOU', 'THEORIGINALS'),
('ITLLDESTROYHIM', 'THEORIGINALS'),
('JEANSSHORTSANDFLIPFLOPS', 'THEORIGINALS'),
('KARI', 'DIGIMON'),
('KINDOFABIGDEAL', 'THEORIGINALS'),
('KIZUNATRAILERA', 'DIGIMON'),
('KIZUNATRAILERB', 'DIGIMON'),
('LEGACIESLOGO', 'LEGACIES'),
('NOTGOINGTOKILLYOU', 'THEORIGINALS'),
('NOTTHISTHINGSPUPPET', 'THEORIGINALS'),
('NOWIHAVETHIS', 'THEORIGINALS'),
('ONEWEAKNESS', 'THEORIGINALS'),
('REMEMBERYOURFAMILY', 'THEORIGINALS'),
('RUNFARANDFAST', 'THEORIGINALS'),
('SHAREDMEMORY', 'THEORIGINALS'),
('SHESDOINGOK', 'THEORIGINALS'),
('SURVIVALDEMANDSSACRIFICE', 'THEORIGINALS'),
('THELITTLEONESYOUSEEK', 'THEORIGINALS'),
('THOSEIMMORTALTYRANTS', 'THEORIGINALS'),
('TRUSTMENOW', 'THEORIGINALS'),
('UNRELENTINGTRAGEDY', 'THEORIGINALS'),
('VAMPIREBOOKCLUB', 'THEORIGINALS'),
('VINCENTVALENTINE', 'FINALFANTASY7'),
('WHATAREYOUPEOPLE', 'THEORIGINALS'),
('WHATEVERTROUBLE', 'THEORIGINALS'),
('WHATYOUREALLYARE', 'THEORIGINALS'),
('WHENISAYFRIEND', 'THEVAMPIREDIARIES'),
('WHOSEM', 'THEVAMPIREDIARIES'),
('YOUCANDOBETTER', 'THEORIGINALS'),
('YOUHEARDOFME', 'THEORIGINALS'),
('YOUREKILLINGTHEMB', 'THEORIGINALS'),
('YOUSEEMTROUBLED', 'THEORIGINALS'),
('YOUWILLPAYFORTHIS', 'THEORIGINALS');


Подробнее здесь: https://stackoverflow.com/questions/796 ... r-category