【SQL】【MySQL】 現在日時に近い順にソートし、重複を排除する

【0】お題

* 現在日時に近い順にソートし、重複を排除する

【1】サンプル

 * 顧客テーブル「customer」と予約テーブル「reservation」があり、
   予約日時に対して、現在日時に近い順にソートし、
 顧客名の重複を排除するデータを取得する

1)テーブル

customer

CREATE TABLE `customer` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(50) NULL DEFAULT NULL,
    `family_name` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=7
;

reservation

CREATE TABLE `reservation` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `reservation_datetime` DATETIME NULL DEFAULT NULL,
    `customer_id` BIGINT(20) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

2)データ

customer

INSERT INTO `customer` (`first_name`, `family_name`) VALUES ('Mike', 'Abel');
INSERT INTO `customer` (`first_name`, `family_name`) VALUES ('Ken', 'Allen');
INSERT INTO `customer` (`first_name`, `family_name`) VALUES ('Kevin', 'Cox');
INSERT INTO `customer` (`first_name`, `family_name`) VALUES ('Smith', 'Abel');
INSERT INTO `customer` (`first_name`, `family_name`) VALUES ('Ken', 'Dean');
INSERT INTO `customer` (`first_name`, `family_name`) VALUES ('Tom', 'Abel');

reservation

INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-04-01 11:00:00', 3);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-06-05 15:30:00', 2);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-07-22 12:30:00', 1);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-06-27 14:30:00', 4);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-06-29 12:30:00', 5);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-07-12 10:00:00', 2);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-08:31 12:30:00', 3);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-08-03 22:30:00', 6);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-08-13 12:30:00', 5);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-07-30 12:30:00', 3);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-06-25 12:30:00', 4);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-07-30 12:30:00', 2);

データ出力結果

"id" "reservation_datetime"  "customer_id"   "first_name"    "family_name"
"1" "2016-04-01 11:00:00"   "3" "Kevin" "Cox"
"2" "2016-06-05 15:30:00"   "2" "Ken"   "Allen"
"3" "2016-07-22 12:30:00"   "1" "Mike"  "Abel"
"4" "2016-06-27 14:30:00"   "4" "Smith" "Abel"
"5" "2016-06-29 12:30:00"   "5" "Ken"   "Dean"
"6" "2016-07-12 10:00:00"   "2" "Ken"   "Allen"
"7" "2016-08-31 12:30:00"   "3" "Kevin" "Cox"
"8" "2016-08-03 22:30:00"   "6" "Tom"   "Abel"
"9" "2016-08-13 12:30:00"   "5" "Ken"   "Dean"
"10"    "2016-07-30 12:30:00"   "3" "Kevin" "Cox"
"11"    "2016-06-25 12:30:00"   "4" "Smith" "Abel"
"12"    "2016-07-30 12:30:00"   "2" "Ken"   "Allen"

3)SQL

SELECT
 c.first_name
FROM
 customer AS c
LEFT OUTER JOIN
 reservation AS r
ON
 c.id = r.customer_id
GROUP BY
 c.first_name
ORDER BY
 MIN(ABS(DATEDIFF(CURRENT_TIME, -- 「CURRENT_TIME」を可変にしてもいい
  CASE WHEN r.reservation_datetime IS NULL
   THEN '9999-12-31 23:59:59'
   ELSE r.reservation_datetime END))),
 MIN(ABS(TIMESTAMPDIFF(SECOND, CURRENT_TIME, -- 「CURRENT_TIME」を可変にしてもいい
  CASE WHEN r.reservation_datetime IS NULL
   THEN '9999-12-31 23:59:59'
   ELSE r.reservation_datetime END)))

1)出力結果

2016-08-03 23:02実行時の結果

"first_name"
"Tom"
"Ken"
"Kevin"
"Mike"
"Smith"

参考文献

https://teratail.com/questions/10971