Кто поможет в составлении сложного запроса mysql?

Question

[Сергей Шевченко]

Всем привет кто сможет помочь в составлении сложного запроса?
Вот структура таблиц, нужно выбрать всех пользователей со знаком зодиака "Козерог"

CREATE TABLE IF NOT EXISTS `horoscope` (
    `id` INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL UNIQUE,
    `date_start` VARCHAR(5),
    `date_end` VARCHAR(5)
);
CREATE INDEX `horoscope_idx_1` ON `horoscope`(`date_start`, `date_end`);

CREATE TABLE IF NOT EXISTS `user` (
    `id` INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `birthday` DATE NOT NULL
);

Сгенерировать тестовые данные

INSERT INTO `horoscope` (`name`, `date_start`, `date_end`) VALUES ('Aries', '03-21', '04-20');
INSERT INTO `horoscope` (`name`, `date_start`, `date_end`) VALUES ('Taurus', '04-21', '05-20');
INSERT INTO `horoscope` (`name`, `date_start`, `date_end`) VALUES ('Gemini', '05-22', '06-21');
INSERT INTO `horoscope` (`name`, `date_start`, `date_end`) VALUES ('Cancer', '06-22', '07-22');
INSERT INTO `horoscope` (`name`, `date_start`, `date_end`) VALUES ('Leo', '07-23', '08-23');
INSERT INTO `horoscope` (`name`, `date_start`, `date_end`) VALUES ('Virgin', '08-24', '09-22');
INSERT INTO `horoscope` (`name`, `date_start`, `date_end`) VALUES ('Libra', '08-23', '10-22');
INSERT INTO `horoscope` (`name`, `date_start`, `date_end`) VALUES ('Scorpio', '10-23', '12-21');
INSERT INTO `horoscope` (`name`, `date_start`, `date_end`) VALUES ('Sagittarius', '11-22', '12-21');
INSERT INTO `horoscope` (`name`, `date_start`, `date_end`) VALUES ('Capricorn', '12-22', '01-20');
INSERT INTO `horoscope` (`name`, `date_start`, `date_end`) VALUES ('Aquarius', '01-21', '02-19');
INSERT INTO `horoscope` (`name`, `date_start`, `date_end`) VALUES ('Pisces', '02-20', '03-20');

# Insert random user in table
DROP PROCEDURE IF EXISTS `add_user`;
CREATE PROCEDURE `add_user`(IN `count_user` INT)
    LANGUAGE SQL
    DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT 'A procedure for inserting random user'

BEGIN
    DECLARE i INT DEFAULT (
        SELECT `id`
        FROM `user`
        ORDER BY `id` DESC
        LIMIT 1
    );
    IF i IS NULL
        THEN SET i = 1;
    END IF;

    SET `count_user` = `count_user` + i;

    WHILE i <= `count_user` DO
        SET @`name` = CONCAT('user_', i);
        SET @`user_birth_day` = LPAD(FLOOR(RAND() * (28 - 1 + 1) + 1), 2, '0');
        SET @`user_birth_mount` = LPAD(FLOOR(RAND() * (12 - 1 + 1) + 1), 2, '0');
        SET @`user_birth_year` = FLOOR(RAND() * (2000 - 1980 + 1) + 1980);
        SET @`user_birth` = CONCAT(@`user_birth_year`, '-', @`user_birth_mount`, '-', @`user_birth_day`);
        INSERT INTO `user` (`name`, `birthday`) VALUES (@`name`, @`user_birth`);

        SET i = i + 1;
    END WHILE;
END;

CALL `add_user`(10000);
DROP PROCEDURE IF EXISTS `add_user`;

Думаю сделать так:
Сначала берем у пользователя дату его рождения и приводим к формату с 1980-12-31 в 1231 и также делаем с зодиаками
например овен ('Aries', '03-21', '04-20'); приводим в 321, 420
И смотрим есть ли наше число 1231 в диапазоне between(321, 420)
И так с каждым. Но тут дошло дело до знака зодиака козерог ('Capricorn', '12-22', '01-20');
То здесь думаю привести так же в 1222, 120 и только два условия будут
от 1222 до (31 декабря) 1231
и в диапазоне (первое января) 101 по 120

Только кто поможет мне это реальзовать?
Заранее всем спасибо за помощь!

Comments

[Модератор]

Сергей Шевченко дорогой пользователь, настоятельно рекомендуем еще раз обратить самое пристальное внимание на п. 3.1 регламента работы сервиса (и, в особенности, на его последний абзац).
В противном случае, ваши вопросы будут удаляться по причине тег-спама, а систематические нарушения приведут к блокировке учетной записи.

[Сергей Шевченко]

Модератор, Так запрос же одинаково будет работать и в sql и в mysql и в posgresql. Почему теги то удалили? Название темы еще соглашусь что некорректно тк там напимал mysql

[Модератор]

Сергей Шевченко, потому что вы сами обозначили "запроса mysql".

[Сергей Шевченко]

Модератор, Поинтересуюсь на будущее...

А если бы я написал Кто поможет в составлении сложного запроса sql?

То можно было бы теги поставить mysql posgresql sql? Ведь это все sql базы данных
Или их надо было все перечислить?
Кто поможет в составлении сложного запроса sql, mysql, posgresql?

Answer 1

[Сергей Шевченко]

Я решил! Вот решение если кому интересно

SELECT *
FROM user as u
WHERE (
    DATE_FORMAT(u.birthday, '%m%d') >= (
        SELECT
            CONCAT(LEFT(`h`.`date_start`, 2), RIGHT(`h`.`date_start`, 2))
        FROM horoscope h
        WHERE h.name = 'Capricorn'
    ) AND DATE_FORMAT(u.birthday, '%m%d') <= 1231
) OR (
    DATE_FORMAT(u.birthday, '%m%d') >= 101 AND DATE_FORMAT(u.birthday, '%m%d') <= (
        SELECT
            CONCAT(LEFT(`h`.`date_end`, 2), RIGHT(`h`.`date_end`, 2))
        FROM horoscope h
        WHERE h.name = 'Capricorn'
    )
);

Answer 2

[Дмитрий Гамзин]

Проверил, теперь все гуд. Спасибо за хороший вопрос.

SELECT * FROM user p LEFT JOIN horoscope ph
       ON 
       (
           convert(MONTH(p.birthday), UNSIGNED INTEGER) >= convert(LEFT(ph.date_start, 2), UNSIGNED INTEGER)
           AND convert(DAYOFMONTH(p.birthday), UNSIGNED INTEGER) >= convert(RIGHT(ph.date_start, 2), UNSIGNED INTEGER) 
           AND convert(DAYOFMONTH(p.birthday), UNSIGNED INTEGER) <= "31"
       )
       OR 
       (
           convert(MONTH(p.birthday), UNSIGNED INTEGER) <= convert(LEFT(ph.date_end, 2), UNSIGNED INTEGER)
           AND convert(DAYOFMONTH(p.birthday), UNSIGNED INTEGER) <= convert(RIGHT(ph.date_end, 2), UNSIGNED INTEGER) 
           AND convert(DAYOFMONTH(p.birthday), UNSIGNED INTEGER) >= "0"
       )
       WHERE ph.name = 'Capricorn'

Comments

[Сергей Шевченко]

Только ты забыл сказать что у гороскопов нету года и нужно любой год им одинаковый дать и изменить тип поля с варчара на дату
И при селекте у пользователей приводить год рождения к этому году который назначил гороскопам, тогда твой запрос (наверное) проканает

[Сергей Шевченко]

Сергей Шевченко, Наверное так и поступим =)

[Сергей Шевченко]

Дмитрий Гамзин, [42000][1305] FUNCTION testwork.TO_CHAR does not exist
БД mysql

[Дмитрий Гамзин]

Сергей Шевченко, спасибо) прошу прощения за ошибки. А вот так?

[Сергей Шевченко]

Переделал твой запрос, ни одной записи не вернулось

SELECT *
FROM user `u`
    LEFT JOIN horoscope `h` ON LEFT(`h`.date_start, 2) < DATE_FORMAT(`u`.birthday, '%d')
        AND LEFT(`h`.date_end, 2) > DATE_FORMAT(`u`.birthday, '%d')
        AND RIGHT(`h`.date_start, 2) < DATE_FORMAT(`u`.birthday, '%m')
        AND RIGHT(`h`.date_end, 2) > DATE_FORMAT(`u`.birthday, '%m')
WHERE `h`.name = 'Aquarius';

[Сергей Шевченко]

Дмитрий Гамзин, Этот тоже не канает, ноль записей

SELECT p.name
FROM user p
    LEFT JOIN horoscope ph
        ON LEFT(ph.date_start, 2) < convert(DAYOFMONTH(p.birthday), CHAR)
        AND LEFT(ph.date_end, 2) > convert(DAYOFMONTH(p.birthday), CHAR)
        AND RIGHT(ph.date_start, 2) < convert(MONTH(p.birthday), CHAR)
        AND RIGHT(ph.date_end, 2) > convert(MONTH(p.birthday), CHAR)
WHERE ph.name = 'Aries';

[Сергей Шевченко]

[Сергей Шевченко]

Дмитрий Гамзин, Немного не то =((

[Дмитрий Гамзин]

Сергей Шевченко, методом проб и ошибок получилось) сейчас у меня забито 7 пользователей, с разными датами, вроде по названию гороскопа все четко выводит. Загвоздка была в том, что функция right отсекала два символа из даты конца знака зодиака, а т.к. значение char, то в начале оставался 0, т.е. получалось 02, 03. Так происходило некорректное сравнение. После этого я все перевел в int, и стало все гуд.