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/2ce79faf-cccf-6f8f-8b07-95d79f6fe0c5.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
SELECT 'city2' AS city, '1994-11-01' AS effect_date,
	SUM(tax_rate) as total_taxes
FROm TaxRates AS R1
WHERE R1.effect_date =
(SELECT MAX(R2.effect_date)
    FROM TaxRates AS R2
    WHERE R2.Effect_date <= '1994-11-01'
    AND R1.tax_authority = R2.tax_authority)
    And R1.tax_authority IN
        ( select tax_authority
            from TaxAuthorities AS A1
            where A1.tax_area = 'city2')
group by tax_authority,effect_date;

-- where句の非相関サブクエリを、SELECTに移し実際のレコードを確認する
-- tax_authority毎のR2.Effect_date <= '1994-11-01'　かつ最大値のeffect_dateを取得している。
SELECT 'city2' AS city, '1994-11-01' AS effect_date_1,tax_rate,
	R1.effect_date,
	(SELECT MAX(R2.effect_date)
    FROM TaxRates AS R2
    WHERE R2.Effect_date <= '1994-11-01'
    AND R1.tax_authority = R2.tax_authority
	)  as sub_query_max_date,
	tax_authority,
	(SELECT MAX(R4.tax_authority)
    FROM TaxRates AS R4
    WHERE R4.Effect_date <= '1994-11-01'
    AND R1.tax_authority = R4.tax_authority
	)  as sub_query_r2_tax_autho
FROm TaxRates AS R1
;

-- where句にR1.effect_date =　サブクエリを加える
-- '1994-11-01'より前で最も近いレコードを条件指定する
SELECT 'city2' AS city, '1994-11-01' AS effect_date_1,
	R1.effect_date,tax_rate,tax_authority
FROm TaxRates AS R1
WHERE R1.effect_date =
(SELECT MAX(R3.effect_date)
    FROM TaxRates AS R3
    WHERE R3.Effect_date <= '1994-11-01'
    AND R1.tax_authority = R3.tax_authority
	)
;


-- 答え
-- Whereに　INを加える
SELECT 'city2' AS city, '1994-11-01' AS effect_date_1,
	R1.effect_date,tax_rate,tax_authority
FROm TaxRates AS R1
WHERE R1.effect_date =
(SELECT MAX(R3.effect_date)
    FROM TaxRates AS R3
    WHERE R3.Effect_date <= '1994-11-01'
    AND R1.tax_authority = R3.tax_authority
	)
    And R1.tax_authority IN
        ( select tax_authority
            from TaxAuthorities AS A1
            where A1.tax_area = 'city2')
;

-- in句以下の相関サブクエリ
 select tax_authority
            from TaxAuthorities AS A1
            where A1.tax_area = 'city2'
;

```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/3c8db445-17e9-8d74-8853-15b9f3a2ec86.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/311df1855a145c63a44e728d7dcef6ad71a80dfc

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

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