SQLパズル演習
SQLパズル演習

パズル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さんの回答

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の部屋も出てくるようになりました。

コメント

名前:
コメント:

すべてのコメントを見る

タグ：

sqlpuzzle 外部結合
+ タグ編集
  • タグ：
  • sqlpuzzle
  • 外部結合

このサイトはreCAPTCHAによって保護されており、Googleの プライバシーポリシー と 利用規約 が適用されます。

タグの更新に失敗しました
エラーが発生しました。ページを更新してください。
ページを更新
「Puzzle22」をウィキ内検索
最終更新：2008年04月02日 12:52