MySQLで期間中の最大個数を担当者毎に集計したい

質問

顧客が要望する期間に望む個数だけ、担当者が商品を貸し出すような業務を記録するために、下記のようなテーブルをMySQLで作っています。

テーブル : t_lental
+---+---+------------+------------+
|man|amt| start_date | end_date |
+---+---+------------+------------+
| A | 3 | 2012-01-01 | 2012-01-09 |
| B | 2 | 2012-01-01 | 2012-01-10 |
| A | 3 | 2012-01-02 | 2012-01-08 |
| B | 4 | 2012-01-02 | 2012-01-12 |
| A | 1 | 2012-01-03 | 2012-01-13 |
| A | 2 | 2012-01-06 | 2012-01-14 |
| A | 1 | 2012-01-14 | 2012-01-22 |
| A | 4 | 2012-01-15 | 2012-01-18 |
| B | 3 | 2012-01-15 | 2012-01-25 |
+---+---+------------+------------+

　　man : 従業員
　　amt : 個数
　　start_date : 期間(自)
　　end_date : 期間(至)

この時、月末締めをするため、その月内で最も要望が重なった期間に貸し出した個数の合計を、担当者毎に求めるにはどうしたらよいでしょうか?
上記テーブルの例では、担当者「A」の場合、2012-01-06から2012-01-08までの2日間が最も要望が重なっていることになります。
結果として得たいイメージは以下のようなものとなります。
+---+---+
|man|ans|
+---+---+
| A | 9 |
| B | 6 |
+---+---+

よろしくお願いいたします。

回答No.1

root139 · ベストアンサー

なかなか骨のある課題でしたが、下記のSQLでいけると思います。
version 5.5.8 で確認しました。

SELECT
man,
MAX(sum_amount) AS ans
FROM
(
SELECT
man,
(SELECT SUM(amt) FROM t_lental b WHERE start_date <= target_date AND end_date >= target_date AND a.man = b.man) AS sum_amount
FROM
(
SELECT DISTINCT man, start_date AS target_date FROM t_lental
UNION
SELECT DISTINCT man, end_date AS target_date FROM t_lental
) a
) c
GROUP BY man;

別名aのサブクエリーで担当者ごとの全ての開始日と終了日の集合を取得しています。
その一つ外側で、各担当者ごとの全ての開始日と終了日について合計の個数を算出しています。
そして、一番外側で担当者ごとの合計の個数が一番多いものを抽出しています。

お礼

早速回答いただきまして、有難うございます。
こちらでもテストしてみまして、希望通りの結果となりました。

お客様からは月をまたがって要望が来る場合もありますし、あるいは月末だけでなく上旬や中旬、下旬の状況といったように折々に集計したいという場合もありますので、お教えいただいたクエリに、指定する任意の期間とオーバーラップしているデータを絞り込む条件を追加してテストしてみまして、こちらも希望通りとなりました。

大変助かりました。重ねて感謝申し上げます。