У меня есть две таблицы:
Quest
- (int) id
- (text) characters
User
- (int) id
- (text) characters
Записи выглядят так:
Квест
id | characters
1 | abcdefgh
2 | mkorti
3 | afoxi
4 | bac
Пользователь
id | characters
1 | abcd
Теперь я хочу выбрать самый простой квест для пользователя. Самый простой квест — это тот, в котором больше всего пересечений quest.characters и user.characters. Итак, в этом примере список будет выглядеть так (для user.id = 1):
questid | easiness
4 | 100
1 | 50
3 | 40
2 | 0
Легкость просто показывает, сколько процентов совпало. Возможно ли с помощью MySQL сделать такие пересечения столбцов? Каково выступление? На самом деле у меня действительно тоже есть отношения (квест -> персонаж и пользователь -> персонажи), однако я думаю, что это не очень эффективно. Так как есть несколько тысяч квестов, а также несколько тысяч персонажей.
Обновление №1
Хорошо, реляционные отношения все еще кажутся подходящими, хорошо. Теперь мои таблицы выглядят так:
CREATE TABLE IF NOT EXISTS `quest` (
`questid` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`questid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
CREATE TABLE IF NOT EXISTS `questcharacters` (
`questid` int(10) unsigned NOT NULL,
`characterid` int(10) unsigned NOT NULL,
PRIMARY KEY (`questid`,`characterid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `single_character` (
`characterid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`single_char` varchar(10) NOT NULL,
PRIMARY KEY (`characterid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `user` (
`userid` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `usercharacters` (
`userid` int(10) unsigned NOT NULL,
`characterid` int(10) unsigned NOT NULL,
PRIMARY KEY (`userid`,`characterid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
PS: Не удивляйтесь, почему single_char
имеет тип данных VARCHAR(10), но я буду использовать многобайтовые значения и не знаю, как MySQL обрабатывает их для char(1). Так что я просто был великодушен там.
Обновление №2
Мой запрос на данный момент:
SELECT usercharacters.userid, questcharacters.questid
FROM `usercharacters`
LEFT OUTER JOIN questcharacters ON usercharacters.characterid = usercharacters.characterid
GROUP BY questcharacters.questid, usercharacters.userid;
Но как рассчитать легкость/перекрывающиеся символы? К какому полю я должен применить COUNT()?
Обновление №3
Хорошо, похоже, я получил работу с этим запросом (использует подзапрос):
SELECT usercharacters.userid as uid, questcharacters.questid as qid, (SELECT COUNT(questcharacters.characterid) FROM questcharacters LEFT OUTER JOIN usercharacters ON questcharacters.characterid = usercharacters.characterid WHERE questcharacters.questid = qid) as questcount
FROM `usercharacters`
LEFT OUTER JOIN questcharacters ON usercharacters.characterid = usercharacters.characterid
GROUP BY questcharacters.questid, usercharacters.userid;
Обновление №4
SELECT usercharacters.userid as uid, questcharacters.questid as qid, (SELECT COUNT(questcharacters.characterid) FROM questcharacters LEFT OUTER JOIN usercharacters ON questcharacters.characterid = usercharacters.characterid WHERE questcharacters.questid = qid) as user_knows, (SELECT COUNT(questcharacters.characterid) FROM questcharacters WHERE questcharacters.questid = qid) as total_characters
FROM `usercharacters`
LEFT OUTER JOIN questcharacters ON usercharacters.characterid = usercharacters.characterid
GROUP BY questcharacters.questid, usercharacters.userid
ORDER BY total_characters / user_knows DESC;
Единственное, чего сейчас не хватает: выбора легкости. (Как в предложении ORDER BY). Кто-нибудь знает, как это сделать?
CHAR(1)
будет содержать один символ в своем наборе символов, независимо от количества байтов, необходимых для хранения закодированного значения. - person eggyal   schedule 15.07.2013