Учитывая следующий набор данных, как мне найти адреса электронной почты, которые были ссылками для большинства ApplicationID
, имеющих решение "Принято"?
CREATE TABLE IF NOT EXISTS 'EmailReferences' (
'ApplicationID' INT NOT NULL,
'Email' VARCHAR(45) NOT NULL,
PRIMARY KEY ('ApplicationID', 'Email')
);
INSERT INTO EmailReferences (ApplicationID, Email)
VALUES
(1, '[email protected]'), (1, '[email protected]'), (1, '[email protected]'),
(2, '[email protected]'), (2, '[email protected]'), (2, '[email protected]'),
(3, '[email protected]'), (3, '[email protected]'), (3, '[email protected]'),
(4, '[email protected]'), (4, '[email protected]'), (4, '[email protected]'),
(5, '[email protected]'), (5, '[email protected]'), (5, '[email protected]'),
(6, '[email protected]'), (6, '[email protected]'), (6, '[email protected]'),
(7, '[email protected]'), (7, '[email protected]'), (7, '[email protected]'),
(8, '[email protected]'), (8, '[email protected]'), (8, '[email protected]')
;
CREATE TABLE IF NOT EXISTS 'FinalDecision' (
'ApplicationID' INT NOT NULL,
'Decision' ENUM('Accepted', 'Denied') NOT NULL,
PRIMARY KEY ('ApplicationID')
);
INSERT INTO FinalDecision (ApplicationID, Decision)
VALUES
(1, 'Accepted'), (2, 'Denied'),
(3, 'Accepted'), (4, 'Denied'),
(5, 'Denied'), (6, 'Denied'),
(7, 'Denied'), (8, 'Accepted')
;
Скрипка того же: http://sqlfiddle.com/#!9/03bcf2/1
Первоначально я использовал LIMIT 1
и ORDER BY CountDecision DESC
, например:
SELECT er.email, COUNT(fd.Decision) AS CountDecision
FROM EmailReferences AS er
JOIN FinalDecision AS fd ON er.ApplicationID = fd.ApplicationID
WHERE fd.Decision = 'Accepted'
GROUP BY er.email
ORDER BY CountDecision DESC
LIMIT 1
;
Однако мне пришло в голову, что у меня может быть несколько адресов электронной почты, на которые ссылаются разные "наиболее приемлемые" решения (т.е. Связь, если можно так выразиться), и они будут отфильтрованы (это правильная фраза?) С ключевым словом LIMIT
,
Затем я попробовал вариант с указанным выше запросом, заменив строки ORDER BY
и LIMIT
:
HAVING MAX(CountDecision)
Но я понял, что только половину утверждения: MAX(CountDecision)
нужно сравнить с чем-то. Я просто не знаю, что.
Любые указатели будут высоко оценены. Спасибо!
Примечание: это для домашнего задания.
Обновление. Чтобы быть ясным, я пытаюсь найти ценность и количество Email
из EmailReferences
. Тем не менее, я хочу только те строки, которые имеют FinalDecision.Decision = 'Accepted'
(на соответствие ApplicantID
s). По моим данным, результат должен быть:
Email | CountDecision
---------------+--------------
[email protected] | 2
[email protected] | 2
Например...
SELECT a.*
FROM
( SELECT x.email
, COUNT(*) total
FROM emailreferences x
JOIN finaldecision y
ON y.applicationid = x.applicationid
WHERE y.decision = 'accepted'
GROUP
BY x.email
) a
JOIN
( SELECT COUNT(*) total
FROM emailreferences x
JOIN finaldecision y
ON y.applicationid = x.applicationid
WHERE y.decision = 'accepted'
GROUP
BY x.email
ORDER
BY total DESC
LIMIT 1
) b
ON b.total = a.total;
MySQL все еще не имеет оконных функций, но когда версия 8 готова к производству, это становится проще. Так что для ссылки fuure или для тех баз данных, как Mariadb, которые уже имеют функции окна:
CREATE TABLE IF NOT EXISTS 'EmailReferences' ( 'ApplicationID' INT NOT NULL, 'Email' VARCHAR(45) NOT NULL, PRIMARY KEY ('ApplicationID', 'Email') );
INSERT INTO EmailReferences (ApplicationID, Email) VALUES (1, '[email protected]'), (1, '[email protected]'), (1, '[email protected]'), (2, '[email protected]'), (2, '[email protected]'), (2, '[email protected]'), (3, '[email protected]'), (3, '[email protected]'), (3, '[email protected]'), (4, '[email protected]'), (4, '[email protected]'), (4, '[email protected]'), (5, '[email protected]'), (5, '[email protected]'), (5, '[email protected]'), (6, '[email protected]'), (6, '[email protected]'), (6, '[email protected]'), (7, '[email protected]'), (7, '[email protected]'), (7, '[email protected]'), (8, '[email protected]'), (8, '[email protected]'), (8, '[email protected]') ;
CREATE TABLE IF NOT EXISTS 'FinalDecision' ( 'ApplicationID' INT NOT NULL, 'Decision' ENUM('Accepted', 'Denied') NOT NULL, PRIMARY KEY ('ApplicationID') );
INSERT INTO FinalDecision (ApplicationID, Decision) VALUES (1, 'Accepted'), (2, 'Denied'), (3, 'Accepted'), (4, 'Denied'), (5, 'Denied'), (6, 'Denied'), (7, 'Denied'), (8, 'Accepted') ;
select email, CountDecision from ( SELECT er.email, COUNT(fd.Decision) AS CountDecision , max(COUNT(fd.Decision)) over() maxCountDecision FROM EmailReferences AS er JOIN FinalDecision AS fd ON er.ApplicationID = fd.ApplicationID WHERE fd.Decision = 'Accepted' GROUP BY er.email ) d where CountDecision = maxCountDecision
email | CountDecision :------------- | ------------: [email protected] | 2
dbfiddle здесь
В основном вам нужно сделать 2 вещи... во-первых, вам нужно найти, что такое maxCount, а затем найти записи с максимальным количеством.
Теперь вы можете объединить эти два шага в одном вложенном запросе или сохранить результат в переменной и использовать ее во втором запросе. Лично я стараюсь избегать внутренних запросов, поскольку они вызывают проблемы с производительностью и более сложны для чтения, поэтому я использую параметр переменной здесь:
-- Find out what max count is and store it in a variable
SELECT @maxcount := COUNT(fd.Decision) AS CountDecision
FROM EmailReferences AS er
JOIN FinalDecision AS fd ON er.ApplicationID = fd.ApplicationID
WHERE fd.Decision = 'Accepted'
GROUP BY er.email
ORDER BY CountDecision desc
Limit 1;
-- get emails with @maxcount
SELECT er.Email, COUNT(fd.Decision) AS CountDecision
FROM EmailReferences AS er
JOIN FinalDecision AS fd ON er.ApplicationID = fd.ApplicationID
WHERE fd.Decision = 'Accepted'
GROUP BY er.email
HAVING COUNT(fd.Decision) = @maxcount;