SQLパズル　p135 32 税金の計算  解法２の学習メモ

# 概要
SQLパズル　p135 32 税金の計算  解法２の学習メモです。
クロス結合を使った解法ですね。
大まかな流れとしては、最初に全ての組み合わせをクロス結合で作り、
WHERE句で市と日付を絞っていくという流れです
自分的には解法１より理解しやすいです。


# 要件

日付、市を検索条件に税率を求める。
税率は州税、郡税、市税で構成されている。


city2市の場合
state1州country2郡city2市なので
state1+country2+city2の税率を取得したい


state:州
country:郡
city:市


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/a0b3f356-b8bc-5ef7-6d56-2c9b60f65ecd.png)


# SQL

## DDL
```sql
DROP TABLE IF EXISTS TaxAuthorities;
CREATE TABLE TaxAuthorities
(tax_authority CHAR(10) NOT NULL,
 tax_area CHAR(10) NOT NULL,
 PRIMARY KEY (tax_authority, tax_area));

DROP TABLE IF EXISTS TaxRates;
CREATE TABLE TaxRates
(tax_authority CHAR(10) NOT NULL,
 effect_date DATE NOT NULL,
 tax_rate DECIMAL (8,2) NOT NULL,
 PRIMARY KEY (tax_authority, effect_date));

--税務当局テーブル
INSERT INTO TaxAuthorities VALUES('city1',   'city1');
INSERT INTO TaxAuthorities VALUES('city2',   'city2');
INSERT INTO TaxAuthorities VALUES('city3',   'city3');
INSERT INTO TaxAuthorities VALUES('county1', 'city1');
INSERT INTO TaxAuthorities VALUES('county1', 'city2');
INSERT INTO TaxAuthorities VALUES('county2', 'city3');
INSERT INTO TaxAuthorities VALUES('state1',  'city1');
INSERT INTO TaxAuthorities VALUES('state1',  'city2');
INSERT INTO TaxAuthorities VALUES('state1',  'city3');

--税率テーブル
INSERT INTO TaxRates VALUES('city1',  '1993-01-01', 1.0);
INSERT INTO TaxRates VALUES('city1',  '1994-01-01', 1.5);
INSERT INTO TaxRates VALUES('city2',  '1993-09-01', 1.5);
INSERT INTO TaxRates VALUES('city2',  '1994-01-01', 2.0);
INSERT INTO TaxRates VALUES('city2',  '1995-01-01', 2.0);
INSERT INTO TaxRates VALUES('city3',  '1993-01-01', 1.7);
INSERT INTO TaxRates VALUES('city3',  '1993-07-01', 1.9);
INSERT INTO TaxRates VALUES('county1', '1993-01-01' , 2.3);
INSERT INTO TaxRates VALUES('county1', '1994-10-01' , 2.5);
INSERT INTO TaxRates VALUES('county1', '1995-01-01' , 2.7);
INSERT INTO TaxRates VALUES('county2', '1993-01-01' , 2.4);
INSERT INTO TaxRates VALUES('county2', '1994-01-01' , 2.7);
INSERT INTO TaxRates VALUES('county2', '1995-01-01' , 2.8);
INSERT INTO TaxRates VALUES('state1' , '1993-01-01' , 0.5);
INSERT INTO TaxRates VALUES('state1' , '1994-01-01' , 0.8);
INSERT INTO TaxRates VALUES('state1' , '1994-07-01' , 0.9);
INSERT INTO TaxRates VALUES('state1' , '1994-10-01' , 1.1);

```

## 答えのSQL

```sql
-- 32 税金の計算　その2
-- 答え
SELECT SUM(T2.tax_rate)
FROM TaxAuthorities AS T1, TaxRates AS T2
	WHERE T1.tax_area = 'city2'
    AND T2.tax_authority = T1.tax_authority
	AND T2.effect_date =
	(SELECT MAX(effect_date)
	FROM TaxRates
	WHERE tax_authority = T2.tax_authority
	AND effect_date <= '1994-11-01'
	)
;


-- クロス結合を作る
SELECT *
FROM TaxAuthorities AS T1, TaxRates AS T2
-- ↓ 検索条件　市
	WHERE T1.tax_area = 'city2'
-- ↓ 結合条件
    AND T2.tax_authority = T1.tax_authority
;


--日付の条件も絞る
SELECT *
FROM TaxAuthorities AS T1, TaxRates AS T2
--  ↓ 検索条件　市
	WHERE T1.tax_area = 'city2'
-- ↓ 結合条件
    AND T2.tax_authority = T1.tax_authority
--  ↓ 検索条件　日付
	AND T2.effect_date =
	(SELECT MAX(effect_date)
	FROM TaxRates
	WHERE tax_authority = T2.tax_authority
	AND effect_date <= '1994-11-01'
	)
;
```


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/e1a39179-e4dd-9681-2212-e02d03078b0e.png)


# 参考

SQLパズル　p135

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

達人に学ぶSQL徹底指南書第２版 なし

github　差分

https://github.com/RYA234/SQL_Puzzle_Learning/commit/9bd410257b87b430e452097d4796aca7e20561cf

【MySQL】非相関サブクエリと相関サブクエリについて
わかりやすい～

https://qiita.com/hukuryo/items/f99982fe75da4481ac2c