Cacher is the code snippet organizer for pro developers

SELECT id, `text`, create_time, GROUP_CONCAT(tag) AS tags
FROM (
    SELECT id, `text`, create_time, COUNT(id) AS tag_count
    FROM notie_text
    JOIN notie_tag ON notie_text.id = notie_tag.notie_id
    WHERE tag IN ('первый тег', 'второй тег', 'третий')
    GROUP BY id
) AS n
JOIN notie_tag ON n.id = notie_tag.notie_id
GROUP BY id
ORDER BY tag_count DESC, create_time DESC;