SQLパズル　p144 34 コンサルタントの請求書 解法2

# 概要
SQLパズル　p144 34 コンサルタントの請求書 解法2の学習メモです。
外部結合とwhere句のサブクエリが焦点？？？

WHERE句の辺りの理解（AND H1.work_date >= bill_date）がたりないから　後で復習かな


# 要件
従業員に払った給料を取得したい。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/8857a431-95b9-5d63-299a-ed24dbc5ea9f.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/ced42ad1-e948-e8dd-628a-feb95b1d78ff.png)


# SQL

## DDL
```sql
drop table if  EXISTS Consultants CASCADE;
CREATE TABLE Consultants
(emp_id INTEGER NOT NULL,
 emp_name CHAR(10) NOT NULL);

drop table if  EXISTS Billings CASCADE;
CREATE TABLE Billings
(emp_id INTEGER NOT NULL,
 bill_date DATE NOT NULL,
 bill_rate DECIMAL (5,2));

drop table if  EXISTS HoursWorked CASCADE;
CREATE TABLE HoursWorked
(job_id INTEGER NOT NULL,
 emp_id INTEGER NOT NULL,
 work_date DATE NOT NULL,
 bill_hrs DECIMAL(5, 2));

INSERT INTO Consultants VALUES (1, 'Larry');
INSERT INTO Consultants VALUES (2, 'Moe');
INSERT INTO Consultants VALUES (3, 'Curly');

INSERT INTO Billings VALUES (1, '1990-01-01', 25.00);
INSERT INTO Billings VALUES (2, '1989-01-01', 15.00);
INSERT INTO Billings VALUES (3, '1989-01-01', 20.00);
INSERT INTO Billings VALUES (1, '1991-01-01', 30.00);

INSERT INTO HoursWorked VALUES (4, 1, '1990-07-01', 3);
INSERT INTO HoursWorked VALUES (4, 1, '1990-08-01', 5);
INSERT INTO HoursWorked VALUES (4, 2, '1990-07-01', 2);
INSERT INTO HoursWorked VALUES (4, 1, '1991-07-01', 4);

```

## 答えのSQL

```sql
-- 34 コンサルタントの請求書　その２
SELECT emp_name,SUM(H1.bill_hrs * B1.bill_rate)
FROM Consultants AS C1,Billings AS B1, Hoursworked AS H1
WHERE C1.emp_id = B1.emp_id
AND C1.emp_id = H1.emp_id
AND bill_date = (SELECT MAX(bill_date)
                FROM Billings AS B2
                WHERE B2.emp_id = C1.emp_id
                AND B2.bill_date <= H1.work_date)
AND H1.work_date >= bill_date
GROUP BY emp_name;


--　where句のサブクエリをSELECT句に持っていき実際の値を確認する
SELECT emp_name,C1.emp_id,H1.work_date,bill_date,
	(SELECT MAX(bill_date)
                FROM Billings AS B2
                WHERE B2.emp_id = C1.emp_id
                AND B2.bill_date <= H1.work_date
	
	) as max_date,
	H1.bill_hrs,B1.bill_rate
FROM Consultants AS C1,Billings AS B1, Hoursworked AS H1
WHERE C1.emp_id = B1.emp_id
AND C1.emp_id = H1.emp_id
-- GROUP BY emp_name
;

-- AND bill_date = (SELECT MAX(bill_date)　以下略　を加える
-- bill_date< work_dateとなるレコードを取り出す
SELECT emp_name,C1.emp_id,H1.work_date,bill_date,
	(SELECT MAX(bill_date)
                FROM Billings AS B2
                WHERE B2.emp_id = C1.emp_id
                AND B2.bill_date <= H1.work_date
	
	) as max_date,
	H1.bill_hrs,B1.bill_rate
FROM Consultants AS C1,Billings AS B1, Hoursworked AS H1
WHERE C1.emp_id = B1.emp_id
AND C1.emp_id = H1.emp_id
AND bill_date = (SELECT MAX(bill_date)
                FROM Billings AS B2
                WHERE B2.emp_id = C1.emp_id
                AND B2.bill_date <= H1.work_date)
--↓の条件要らない？？？？
--AND H1.work_date >= bill_date
;

```


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/665b1076-8641-e7ab-2090-0d15c0edc202.png)


# 参考

SQLパズル　p144

SQLパズル　サポートページ
https://mickindex.sakura.ne.jp/database/db_support_sqlpuzzle.html#LocalLink-p11

達人に学ぶSQL徹底指南書第２版　見当たらない！

github差分

https://github.com/RYA234/SQL_Puzzle_Learning/commit/a02e073a28fdacf7aad4bfd5f3534840f72349b2