【SQL Server】サブクエリを使用したデータ取得/INSERTなど

サブクエリを使用したデータ取得方法についてを記載致します。
下記テーブルを使用して、検証していこうと思います。

CREATE TABLE VANILLA(

	code INT UNIQUE,
	name VARCHAR(40),
	level INT,
	attribute VARCHAR(10),
 	type VARCHAR(10),
	attack INT,
	defense INT,

);	

INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (1,'ウィング・エッグ・エルフ',3,'光','天使族',500,1300);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (2,'カース・オブ・ドラゴン',5,'闇','ドラゴン族',2000,1500);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (3,'岩石の巨兵',3,'地','岩石族',1300,2000);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (4,'クレイジー・フィッシュ',4,'水','魚族',1600,1200);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (5,'ケンタウロス',4,'地','獣族',1300,1550);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (6,'ゴキボール',4,'地','昆虫族',1200,1400);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (7,'サイクロプス',4,'地','獣戦士族',1200,1000);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (8,'シルバー・フォング',3,'地','獣族',1200,800);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (9,'地を這うドラゴン',5,'地','ドラゴン族',1600,1400);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (10,'デーモンの召喚',6,'闇','悪魔族',2500,1200);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (11,'砦を守る翼竜',4,'風','ドラゴン族',1400,1200);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (12,'ハーピィ・レディ',4,'風','鳥獣族',1300,1400);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (13,'マンモスの墓場',3,'地','恐竜族',1200,800);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (14,'モリンフェン',5,'闇','悪魔族',1550,1300);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (15,'ルイーズ',4,'地','獣戦士族',1200,1500);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (16,'真紅眼の黒竜',7,'闇','ドラゴン族',2400,2000);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (17,'闇・道化師のサギー',3,'闇','魔法使い族',600,1500);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (18,'プチテンシ',3,'光','天使族',600,900);
INSERT INTO VANILLA (code,name,level,attribute,type,attack,defense) VALUES (19,'暗黒騎士ガイア',7,'地','戦士族',2300,2100);

クエリ内に記載されたクエリの事をサブクエリと呼びます。
サブクエリが使用されているSQLは、サブクエリ実行後にメインクエリが実行されます。
例えば、平均値以上のattackを取得したい場合は下記のようなSQLを実行する事で取得出来ます。
()で囲まれた部分がサブクエリとなります。

WHERE句

1> SELECT * FROM VANILLA WHERE attack >= (SELECT AVG(attack) FROM VANILLA);
2> GO
code        name                                     level       attribute  type       attack      defense
----------- ---------------------------------------- ----------- ---------- ---------- ----------- -----------
          2 カース・オブ・ドラゴン                   5           闇         ドラゴン族 2000        1500
          4 クレイジー・フィッシュ                   4           水         魚族       1600        1200
          9 地を這うドラゴン                         5           地         ドラゴン族 1600        1400
         10 デーモンの召喚                           6           闇         悪魔族     2500        1200
         14 モリンフェン                             5           闇         悪魔族     1550        1300
         16 真紅眼の黒竜                             7           闇         ドラゴン族 2400        2000
         19 暗黒騎士ガイア                           7           地         戦士族     2300        2100

また、HAVING句の中でもサブクエリを使用する事が可能です。
level毎のattackの最大値 >= attackの平均値以上 の値を取得したい場合は下記で取得出来ます。

HAVING句

1> SELECT level,MAX(attack) as max_attack FROM VANILLA GROUP BY level HAVING MAX(attack) >= (SELECT AVG(attack) FROM VANILLA)
2> GO
level       max_attack
----------- -----------
          4        1600
          5        2000
          6        2500
          7        2400

FROM句内でもサブクエリを使用出来るようです。
ちなみにサブクエリにAS演算子をつけてあげないと、エラーになります。

サブクエリで、attack値が1500以上のデータを取得し、ATK　という別名をつけてあげて、、、
取得したATKから、平均値を算出します。

From句

1> SELECT AVG(attack) AS AVG_ATK FROM (SELECT * FROM VANILLA WHERE attack >= 1500) AS ATK;
2> GO
AVG_ATK
-----------
       1992

ちなみに、、INSERT文やDELETE文等でも使用出来ます。
下記のINSERT文では、サブクエリでattackの平均値を取得。そして、平均値以上のattackのデータを別のテーブルにINSERTしています。

INSERT文

1> INSERT INTO VANILLA_2 SELECT * FROM VANILLA WHERE attack >= (SELECT AVG(attack)  FROM VANILLA)
2> GO

(7 行処理されました)

1> SELECT AVG(attack) FROM VANILLA
2> GO

-----------
       1418

1> SELECT * FROM VANILLA_2
2> GO
code        name                                     level       attribute  type       attack      defense
----------- ---------------------------------------- ----------- ---------- ---------- ----------- -----------
          2 カース・オブ・ドラゴン                    5          闇        ドラゴン族  2000        1500
          4 クレイジー・フィッシュ                    4          水        魚族        1600        1200
          9 地を這うドラゴン                          5          地        ドラゴン族  1600        1400
         10 デーモンの召喚                            6          闇        悪魔族      2500        1200
         14 モリンフェン                              5          闇        悪魔族      1550        1300
         16 真紅眼の黒竜                              7          闇        ドラゴン族  2400        2000
         19 暗黒騎士ガイア                            7          地        戦士族      2300        2100

DELETE文で、defenseの最大値よりattackの値が低いデータを削除します。
下記DELETE文を実行します。

DELETE文

1> DELETE FROM VANILLA_2 WHERE attack < (SELECT MAX(defense) FROM VANILLA_2)
2> GO

(4 行処理されました)
1> SELECT * FROM VANILLA_2
2> GO
code        name                                     level       attribute  type       attack      defense
----------- ---------------------------------------- ----------- ---------- ---------- ----------- -----------
         10 デーモンの召喚                           6           闇         悪魔族     2500        1200
         16 真紅眼の黒竜                             7           闇        ドラゴン族  2400        2000
         19 暗黒騎士ガイア                           7           地         戦士族     2300        2100

(3 行処理されました)

そのうちPowerShell等を使用して、DBにデータ連携するような処理を書いてみようかなと思っています。何かしらの形にするのが一番楽しいですからね。

以上、何かの参考になれば幸いです。