「Puzzle22」の編集履歴（バックアップ）一覧はこちら
「Puzzle22」(2008/04/02 (水) 12:52:40) の最新版変更点
追加された行は緑色になります。
削除された行は赤色になります。
*パズル22：大家の悩み
**問題について
マンションの住人のうち家賃を払った人物についてのレポートを作りたいが考えたSQLではうまくいかないのでその原因と対策を説明したほしいという問題。
ちょっと問題文が読みづらくて、何を求めたいのかがよく分からなかったのですが、各部屋に対して、借主がある期間内に支払っているかもしくは支払っていないかを対応付ける表がほしいみたい。多分答えとしてほしいのはこんなのだと思われます。
+----------+-----------+--------------+--------------+
| unit_nbr | tenant_id | vacated_date | payment_date |
+----------+-----------+--------------+--------------+
| 1 | 1 | NULL | 2007-03-01 |
| 2 | 1 | NULL | NULL |
+----------+-----------+--------------+--------------+
つまり部屋(Unit)に対して借りてる人(tenant）が期間内に払ったらpayment_dateに日付がはいって、そうでなかったらNULLになる。ということだと思われます。
- (unit_nbr,tenant_id)=(1,1)はちゃんと支払われている
- (unit_nbr,tenant_id)=(2,1)は支払われてない
**ちえみんの回答
まずミックさんのサポートページからサンプルデータをもらってきましたが、
期間外に支払われた場合が存在しないのでちょっと付け足してみました。
[[サンプルデータ：Puzzle22]]
select * from Units;
+------------+----------+
| complex_id | unit_nbr |
+------------+----------+
| 32 | 1 |
| 32 | 2 |
| 32 | 3 |
| 32 | 4 |
+------------+----------+
select * from RentPayments;
+-----------+----------+--------------+
| tenant_id | unit_nbr | payment_date |
+-----------+----------+--------------+
| 1 | 1 | 2007-03-01 |
| 1 | 4 | 2007-02-01 |
+-----------+----------+--------------+
select * from Units;
+------------+----------+
| complex_id | unit_nbr |
+------------+----------+
| 32 | 1 |
| 32 | 2 |
| 32 | 3 |
| 32 | 4 |
+------------+----------+
まずは結合の内側から確認してみました。
SELECT *
FROM Tenants T1
LEFT OUTER JOIN RentPayments RP1
ON T1.tenant_id = RP1.tenant_id;
+-----------+----------+--------------+-----------+----------+--------------+
| tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+-----------+----------+--------------+-----------+----------+--------------+
| 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
| 1 | 1 | NULL | 1 | 4 | 2007-02-01 |
| 1 | 2 | NULL | 1 | 1 | 2007-03-01 |
| 1 | 2 | NULL | 1 | 4 | 2007-02-01 |
| 1 | 3 | 2007-01-01 | 1 | 1 | 2007-03-01 |
| 1 | 3 | 2007-01-01 | 1 | 4 | 2007-02-01 |
+-----------+----------+--------------+-----------+----------+--------------+
SELECT *
FROM Tenants T1
LEFT OUTER JOIN RentPayments RP1
ON T1.tenant_id = RP1.tenant_id
WHERE T1.unit_nbr = RP1.unit_nbr;
+-----------+----------+--------------+-----------+----------+--------------+
| tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+-----------+----------+--------------+-----------+----------+--------------+
| 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
+-----------+----------+--------------+-----------+----------+--------------+
1 row in set (0.00 sec)
上の例では左外部結合の結果を示し、そのテーブルから部屋番号が共通するものを選択しています。明らかに左結合した部分が無駄になっている（汗、ていうかなにしたいかよくわかりません。
おかしなことになる原因は結合の条件であり、左結合するときの条件に部屋番号が一致する（T1.unit_nbr=RP1.unit_nbr）条件を入れる必要があります。
SELECT * FROM Tenants T1
LEFT OUTER JOIN RentPayments RP1
ON T1.tenant_id = RP1.tenant_id AND T1.unit_nbr=RP1.unit_nbr;
+-----------+----------+--------------+-----------+----------+--------------+
| tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+-----------+----------+--------------+-----------+----------+--------------+
| 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
| 1 | 2 | NULL | NULL | NULL | NULL |
| 1 | 3 | 2007-01-01 | NULL | NULL | NULL |
| 1 | 4 | NULL | 1 | 4 | 2007-02-01 |
+-----------+----------+--------------+-----------+----------+--------------+
4 rows in set (0.00 sec)
そうすると借主が支払いをしていない部屋番号2と3はちゃんとpayment_dateがNULLになっています。
次に期間内に支払ったという条件を入れるのですが、このままでは期間内に支払ってない部屋が結果テーブルから消えてしまいます。
SELECT *
FROM Tenants T1
LEFT OUTER JOIN RentPayments RP1
ON T1.tenant_id = RP1.tenant_id AND T1.unit_nbr=RP1.unit_nbr
WHERE (RP1.payment_date >= '2007-03-01' and RP1.payment_date < '2007-03-31')
or RP1.payment_date IS NULL;
+-----------+----------+--------------+-----------+----------+--------------+
| tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+-----------+----------+--------------+-----------+----------+--------------+
| 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
| 1 | 2 | NULL | NULL | NULL | NULL |
| 1 | 3 | 2007-01-01 | NULL | NULL | NULL |
+-----------+----------+--------------+-----------+----------+--------------+
3 rows in set (0.00 sec)
なので結合する前に絞っておきます。
SELECT * FROM Tenants T1
LEFT OUTER JOIN
(SELECT * FROM RentPayments
WHERE payment_date>='2007-03-01' and payment_date <'2007-03-31') RP1
ON T1.tenant_id = RP1.tenant_id AND T1.unit_nbr=RP1.unit_nbr;
+-----------+----------+--------------+-----------+----------+--------------+
| tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+-----------+----------+--------------+-----------+----------+--------------+
| 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
| 1 | 2 | NULL | NULL | NULL | NULL |
| 1 | 3 | 2007-01-01 | NULL | NULL | NULL |
| 1 | 4 | NULL | NULL | NULL | NULL |
+-----------+----------+--------------+-----------+----------+--------------+
4 rows in set (0.00 sec)
これで期間内に支払していない部屋番号4がちゃんと出てくるようになります。
というわけで答えは以下の通り。
SELECT *
FROM Units U1 JOIN
(Tenants T1 LEFT OUTER JOIN
(SELECT * FROM RentPayments
WHERE payment_date>='2007-03-01' and payment_date<'2007-03-31') RP1
ON T1.tenant_id=RP1.tenant_id AND T1.unit_nbr=RP1.unit_nbr)
ON U1.unit_nbr = T1.unit_nbr
WHERE T1.vacated_date IS NULL
ORDER BY U1.unit_nbr, RP1.payment_date;
+------------+----------+-----------+----------+--------------+-----------+----------+--------------+
| complex_id | unit_nbr | tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+------------+----------+-----------+----------+--------------+-----------+----------+--------------+
| 32 | 1 | 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
| 32 | 2 | 1 | 2 | NULL | NULL | NULL | NULL |
| 32 | 4 | 1 | 4 | NULL | NULL | NULL | NULL |
+------------+----------+-----------+----------+--------------+-----------+----------+--------------+
3 rows in set (0.00 sec)
**fumikoさんの回答
#ref(2008年04月01日13時24分21秒.pdf)
-問題の意図がつかめなかったので、解答もちょっとむにゃむにゃになってしまったようです。
**書籍の解答例を見て
-解答例
SELECT *
FROM(Units AS U1 LEFT OUTER JOIN Tenants AS T1
ON U1.unit_nbr = T1.unit_nbr
AND T1.vacated_date IS NULL
AND U1.complex_id = 32)
LEFT OUTER JOIN RentPayments AS RP1
ON (T1.tenant_id = RP1.tenant_id
AND U1.unit_nbr = RP1.unit_nbr)
WHERE RP1.payment_date BETWEEN :my_start_date
AND :my_end_date
OR RP1.payment_date IS NULL;
-回答例は1つしかなかったが、これだと期間外に支払があった場合のタプルがなくなってしまうのでは？（chiemin）
-ただしchieminの解答では現在借主がいない部屋が出てこない（chiaki）
-でも入居してないんだから出てこなくてもいいのでは（chiemin）
-とりあえず入居してない部屋も出てくるようにしてみた。
SELECT *
FROM (Units U1 LEFT JOIN Tenants T1
ON U1.unit_nbr = T1.unit_nbr
AND T1.vacated_date IS NULL
AND U1.complex_id=32)
LEFT OUTER JOIN
(SELECT * FROM RentPayments
WHERE payment_date='2007-03-01'
and payment_date<'2007-03-31') RP1
ON T1.tenant_id=RP1.tenant_id AND U1.unit_nbr=RP1.unit_nbr
ORDER BY U1.unit_nbr,RP1.payment_date;
+------------+----------+-----------+----------+--------------+-----------+----------+--------------+
| complex_id | unit_nbr | tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+------------+----------+-----------+----------+--------------+-----------+----------+--------------+
| 32 | 1 | 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
| 32 | 2 | 1 | 2 | NULL | NULL | NULL | NULL |
| 32 | 3 | NULL | NULL | NULL | NULL | NULL | NULL |
| 32 | 4 | 1 | 4 | NULL | NULL | NULL | NULL |
+------------+----------+-----------+----------+--------------+-----------+----------+--------------+
4 rows in set (0.00 sec)
現在入居者のいないunits_nbr=3の部屋も出てくるようになりました。
*コメント
#comment_num2
*パズル22：大家の悩み
**問題について
マンションの住人のうち家賃を払った人物についてのレポートを作りたいが考えたSQLではうまくいかないのでその原因と対策を説明したほしいという問題。
ちょっと問題文が読みづらくて、何を求めたいのかがよく分からなかったのですが、各部屋に対して、借主がある期間内に支払っているかもしくは支払っていないかを対応付ける表がほしいみたい。多分答えとしてほしいのはこんなのだと思われます。
+----------+-----------+--------------+--------------+
| unit_nbr | tenant_id | vacated_date | payment_date |
+----------+-----------+--------------+--------------+
| 1 | 1 | NULL | 2007-03-01 |
| 2 | 1 | NULL | NULL |
+----------+-----------+--------------+--------------+
つまり部屋(Unit)に対して借りてる人(tenant）が期間内に払ったらpayment_dateに日付がはいって、そうでなかったらNULLになる。ということだと思われます。
- (unit_nbr,tenant_id)=(1,1)はちゃんと支払われている
- (unit_nbr,tenant_id)=(2,1)は支払われてない
**ちえみんの回答
まずミックさんのサポートページからサンプルデータをもらってきましたが、
期間外に支払われた場合が存在しないのでちょっと付け足してみました。
[[サンプルデータ：Puzzle22]]
select * from Units;
+------------+----------+
| complex_id | unit_nbr |
+------------+----------+
| 32 | 1 |
| 32 | 2 |
| 32 | 3 |
| 32 | 4 |
+------------+----------+
select * from RentPayments;
+-----------+----------+--------------+
| tenant_id | unit_nbr | payment_date |
+-----------+----------+--------------+
| 1 | 1 | 2007-03-01 |
| 1 | 4 | 2007-02-01 |
+-----------+----------+--------------+
select * from Units;
+------------+----------+
| complex_id | unit_nbr |
+------------+----------+
| 32 | 1 |
| 32 | 2 |
| 32 | 3 |
| 32 | 4 |
+------------+----------+
まずは結合の内側から確認してみました。
SELECT *
FROM Tenants T1
LEFT OUTER JOIN RentPayments RP1
ON T1.tenant_id = RP1.tenant_id;
+-----------+----------+--------------+-----------+----------+--------------+
| tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+-----------+----------+--------------+-----------+----------+--------------+
| 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
| 1 | 1 | NULL | 1 | 4 | 2007-02-01 |
| 1 | 2 | NULL | 1 | 1 | 2007-03-01 |
| 1 | 2 | NULL | 1 | 4 | 2007-02-01 |
| 1 | 3 | 2007-01-01 | 1 | 1 | 2007-03-01 |
| 1 | 3 | 2007-01-01 | 1 | 4 | 2007-02-01 |
+-----------+----------+--------------+-----------+----------+--------------+
SELECT *
FROM Tenants T1
LEFT OUTER JOIN RentPayments RP1
ON T1.tenant_id = RP1.tenant_id
WHERE T1.unit_nbr = RP1.unit_nbr;
+-----------+----------+--------------+-----------+----------+--------------+
| tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+-----------+----------+--------------+-----------+----------+--------------+
| 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
+-----------+----------+--------------+-----------+----------+--------------+
1 row in set (0.00 sec)
上の例では左外部結合の結果を示し、そのテーブルから部屋番号が共通するものを選択しています。明らかに左結合した部分が無駄になっている（汗、ていうかなにしたいかよくわかりません。
おかしなことになる原因は結合の条件であり、左結合するときの条件に部屋番号が一致する（T1.unit_nbr=RP1.unit_nbr）条件を入れる必要があります。
SELECT * FROM Tenants T1
LEFT OUTER JOIN RentPayments RP1
ON T1.tenant_id = RP1.tenant_id AND T1.unit_nbr=RP1.unit_nbr;
+-----------+----------+--------------+-----------+----------+--------------+
| tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+-----------+----------+--------------+-----------+----------+--------------+
| 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
| 1 | 2 | NULL | NULL | NULL | NULL |
| 1 | 3 | 2007-01-01 | NULL | NULL | NULL |
| 1 | 4 | NULL | 1 | 4 | 2007-02-01 |
+-----------+----------+--------------+-----------+----------+--------------+
4 rows in set (0.00 sec)
そうすると借主が支払いをしていない部屋番号2と3はちゃんとpayment_dateがNULLになっています。
次に期間内に支払ったという条件を入れるのですが、このままでは期間内に支払ってない部屋が結果テーブルから消えてしまいます。
SELECT *
FROM Tenants T1
LEFT OUTER JOIN RentPayments RP1
ON T1.tenant_id = RP1.tenant_id AND T1.unit_nbr=RP1.unit_nbr
WHERE (RP1.payment_date >= '2007-03-01' and RP1.payment_date < '2007-03-31')
or RP1.payment_date IS NULL;
+-----------+----------+--------------+-----------+----------+--------------+
| tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+-----------+----------+--------------+-----------+----------+--------------+
| 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
| 1 | 2 | NULL | NULL | NULL | NULL |
| 1 | 3 | 2007-01-01 | NULL | NULL | NULL |
+-----------+----------+--------------+-----------+----------+--------------+
3 rows in set (0.00 sec)
なので結合する前に絞っておきます。
SELECT * FROM Tenants T1
LEFT OUTER JOIN
(SELECT * FROM RentPayments
WHERE payment_date>='2007-03-01' and payment_date <'2007-03-31') RP1
ON T1.tenant_id = RP1.tenant_id AND T1.unit_nbr=RP1.unit_nbr;
+-----------+----------+--------------+-----------+----------+--------------+
| tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+-----------+----------+--------------+-----------+----------+--------------+
| 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
| 1 | 2 | NULL | NULL | NULL | NULL |
| 1 | 3 | 2007-01-01 | NULL | NULL | NULL |
| 1 | 4 | NULL | NULL | NULL | NULL |
+-----------+----------+--------------+-----------+----------+--------------+
4 rows in set (0.00 sec)
これで期間内に支払していない部屋番号4がちゃんと出てくるようになります。
というわけで答えは以下の通り。
SELECT *
FROM Units U1 JOIN
(Tenants T1 LEFT OUTER JOIN
(SELECT * FROM RentPayments
WHERE payment_date>='2007-03-01' and payment_date<'2007-03-31') RP1
ON T1.tenant_id=RP1.tenant_id AND T1.unit_nbr=RP1.unit_nbr)
ON U1.unit_nbr = T1.unit_nbr
WHERE T1.vacated_date IS NULL
ORDER BY U1.unit_nbr, RP1.payment_date;
+------------+----------+-----------+----------+--------------+-----------+----------+--------------+
| complex_id | unit_nbr | tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+------------+----------+-----------+----------+--------------+-----------+----------+--------------+
| 32 | 1 | 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
| 32 | 2 | 1 | 2 | NULL | NULL | NULL | NULL |
| 32 | 4 | 1 | 4 | NULL | NULL | NULL | NULL |
+------------+----------+-----------+----------+--------------+-----------+----------+--------------+
3 rows in set (0.00 sec)
**fumikoさんの回答
#ref(2008年04月01日13時24分21秒.pdf)
-問題の意図がつかめなかったので、解答もちょっとむにゃむにゃになってしまったようです。
**書籍の解答例を見て
-回答例は1つしかなかったが、これだと期間外に支払があった場合のタプルがなくなってしまうのでは？（chiemin）
-ただしchieminの解答では現在借主がいない部屋が出てこない（chiaki）
-でも入居してないんだから出てこなくてもいいのでは（chiemin）
-とりあえず入居してない部屋も出てくるようにしてみた。
SELECT *
FROM (Units U1 LEFT JOIN Tenants T1
ON U1.unit_nbr = T1.unit_nbr
AND T1.vacated_date IS NULL
AND U1.complex_id=32)
LEFT OUTER JOIN
(SELECT * FROM RentPayments
WHERE payment_date='2007-03-01'
and payment_date<'2007-03-31') RP1
ON T1.tenant_id=RP1.tenant_id AND U1.unit_nbr=RP1.unit_nbr
ORDER BY U1.unit_nbr,RP1.payment_date;
+------------+----------+-----------+----------+--------------+-----------+----------+--------------+
| complex_id | unit_nbr | tenant_id | unit_nbr | vacated_date | tenant_id | unit_nbr | payment_date |
+------------+----------+-----------+----------+--------------+-----------+----------+--------------+
| 32 | 1 | 1 | 1 | NULL | 1 | 1 | 2007-03-01 |
| 32 | 2 | 1 | 2 | NULL | NULL | NULL | NULL |
| 32 | 3 | NULL | NULL | NULL | NULL | NULL | NULL |
| 32 | 4 | 1 | 4 | NULL | NULL | NULL | NULL |
+------------+----------+-----------+----------+--------------+-----------+----------+--------------+
4 rows in set (0.00 sec)
現在入居者のいないunits_nbr=3の部屋も出てくるようになりました。
*コメント
#comment_num2
