java 复杂 sql_如何编写这个复杂的查询SQL？

如果我知道如何过滤黄道十二宫的所有迹象，那么通过生肖'摩羯座'的标志(从12月22日到1月20日)，我不知道如何选择 .

这是表格的结构，我是否需要选择所有具有黄道带“摩羯座”标志的用户？

可以更改表的结构(如果需要，甚至可以添加新表)：

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 test data

# Insert horoscope in table

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', '11-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` = '1980-01-01' + INTERVAL (RAND() * 365 * 20) DAY;

INSERT INTO `user` (`name`, `birthday`) VALUES (@`name`, @`user_birth`);

SET i = i + 1;

END WHILE;

END;

CALL `add_user`(1000);

DROP PROCEDURE IF EXISTS `add_user`;