例としてMS SQL Server方言を使用したSQL言語チュートリアル（DDL、DML）。 パート4

前のパーツ

• パート1-habrahabr.ru/post/255361
• パート2-habrahabr.ru/post/255523
• パート3-habrahabr.ru/post/255825

この部分では、考慮します

• テーブル結合結合-結合
• WHERE句を使用してテーブルをリンクする
• 垂直マージクエリの結果-UNION

• FROM、SELECTブロックのサブクエリ
• APPLYコンストラクトのサブクエリ
• WITH句を使用する
• WHEREブロックのサブクエリ：
  
  • グループ比較-ALL、ANY
  • 存在する条件
  • 条件IN

いくつかの新しいデータを追加します。

SET IDENTITY_INSERT Departments ON INSERT Departments(ID,Name) VALUES(4,N'  ') INSERT Departments(ID,Name) VALUES(5,N'') SET IDENTITY_INSERT Departments OFF 

 SET IDENTITY_INSERT Positions ON INSERT Positions(ID,Name) VALUES(5,N'') INSERT Positions(ID,Name) VALUES(6,N'') INSERT Positions(ID,Name) VALUES(7,N'') SET IDENTITY_INSERT Positions OFF 

JOIN接続-水平データ接続操作

1. JOIN -left_table JOIN right_table ON join_condition
2. LEFT JOIN -left_table LEFT JOIN right_table ON join_conditions
3. RIGHT JOIN -left_table RIGHT JOIN right_table ON join_conditions
4. FULL JOIN -left_table FULL JOIN right_table ON join_condition
5. CROSS JOIN -left_table CROSS JOIN right_table

1. これは短く、不要な単語でクエリを詰まらせません。
2. LEFT、RIGHT、FULLおよびCROSSによると、JOINの場合、どのような接続について話しているのかが明確です。
3. この場合のINNERとOUTERという言葉は、初心者をより混乱させる不必要な初歩的なものだと思います。

 -- JOIN  5  SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp JOIN Departments dep ON emp.DepartmentID=dep.ID 

 -- LEFT JOIN  6  SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID 

 -- RIGHT JOIN  7  SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp RIGHT JOIN Departments dep ON emp.DepartmentID=dep.ID 

 -- FULL JOIN  8  SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp FULL JOIN Departments dep ON emp.DepartmentID=dep.ID 

 -- CROSS JOIN  30  - (6   Employees) * (5   Departments) SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp CROSS JOIN Departments dep 

テーブルのエイリアスを覚えるときです

 SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp JOIN Departments dep ON emp.DepartmentID=dep.ID 

 SELECT Employees.ID,Employees.Name,Employees.DepartmentID,Departments.ID,Departments.Name FROM Employees JOIN Departments ON Employees.DepartmentID=Departments.ID 

 SELECT e1.ID EmpID1, e1.Name EmpName1, e2.ID EmpID2, e2.Name EmpName2 FROM Employees e1 LEFT JOIN Employees e2 ON e1.ID=e2.ID+1 --     

各タイプの水平接続を分解します

 CREATE TABLE LeftTable( LCode int, LDescr varchar(10) ) GO CREATE TABLE RightTable( RCode int, RDescr varchar(10) ) GO INSERT LeftTable(LCode,LDescr)VALUES (1,'L-1'), (2,'L-2'), (3,'L-3'), (5,'L-5') INSERT RightTable(RCode,RDescr)VALUES (2,'B-2'), (3,'B-3'), (4,'B-4') 

 SELECT * FROM LeftTable 

 SELECT * FROM RightTable 

参加する

 SELECT l.*,r.* FROM LeftTable l JOIN RightTable r ON l.LCode=r.RCode 

左から参加

 SELECT l.*,r.* FROM LeftTable l LEFT JOIN RightTable r ON l.LCode=r.RCode 

正しい参加

 SELECT l.*,r.* FROM LeftTable l RIGHT JOIN RightTable r ON l.LCode=r.RCode 

 SELECT l.*,r.* FROM RightTable r LEFT JOIN LeftTable l ON l.LCode=r.RCode 

FULL JOINは、本質的にLEFT JOINとRIGHT JOINの同時参加です。

 SELECT l.*,r.* FROM LeftTable l FULL JOIN RightTable r ON l.LCode=r.RCode 

クロスジョイン

 SELECT l.*,r.* FROM LeftTable l CROSS JOIN RightTable r 

EmployeesおよびDepartmentsテーブルに戻ります

 -- JOIN  5  SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp JOIN Departments dep ON emp.DepartmentID=dep.ID 

 -- LEFT JOIN  6  SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID 

 -- RIGHT JOIN  7  SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp RIGHT JOIN Departments dep ON emp.DepartmentID=dep.ID 

 -- FULL JOIN  8  SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp FULL JOIN Departments dep ON emp.DepartmentID=dep.ID 

 -- CROSS JOIN  30  - (6   Employees) * (5   Departments) SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp CROSS JOIN Departments dep 

 SELECT dep.ID,dep.Name,emp.ID,emp.Name FROM Employees emp RIGHT JOIN Departments dep ON emp.DepartmentID=dep.ID 

 SELECT dep.ID,dep.Name,emp.ID,emp.Name FROM Employees emp /*      ( - MAX(ID))      (GROUP BY DepartmentID) */ JOIN ( SELECT MAX(ID) MaxEmployeeID FROM Employees GROUP BY DepartmentID ) lastEmp ON emp.ID=lastEmp.MaxEmployeeID RIGHT JOIN Departments dep ON emp.DepartmentID=dep.ID --   Departments 

 SELECT MAX(ID) MaxEmployeeID FROM Employees GROUP BY DepartmentID 

材料を統合する独立した作業

 --   LeftTable  RightTable TRUNCATE TABLE LeftTable TRUNCATE TABLE RightTable GO --       INSERT LeftTable(LCode,LDescr)VALUES (1,'L-1'), (2,'L-2a'), (2,'L-2b'), (3,'L-3'), (5,'L-5') INSERT RightTable(RCode,RDescr)VALUES (2,'B-2a'), (2,'B-2b'), (3,'B-3'), (4,'B-4') 

 SELECT * FROM LeftTable 

 SELECT * FROM RightTable 

 SELECT l.*,r.* FROM LeftTable l JOIN RightTable r ON l.LCode=r.RCode 

 SELECT l.*,r.* FROM LeftTable l LEFT JOIN RightTable r ON l.LCode=r.RCode 

 SELECT l.*,r.* FROM LeftTable l RIGHT JOIN RightTable r ON l.LCode=r.RCode 

 SELECT l.*,r.* FROM LeftTable l FULL JOIN RightTable r ON l.LCode=r.RCode 

 SELECT l.*,r.* FROM LeftTable l CROSS JOIN RightTable r 

JOIN接続についてもう一度

 SELECT e.ID, e.Name EmployeeName, p.Name PositionName, d.Name DepartmentName FROM Employees e LEFT JOIN Departments d ON e.DepartmentID=d.ID LEFT JOIN Positions p ON e.PositionID=p.ID 

 SELECT e.* FROM Employees e -- 1 

 SELECT e.*, --   Employees d.* --   (e.DepartmentID=d.ID)  Departments FROM Employees e -- 1 LEFT JOIN Departments d ON e.DepartmentID=d.ID -- 2 

 SELECT e.*, --   Employees d.*, --   (e.DepartmentID=d.ID)  Departments p.* --   (e.PositionID=p.ID)  Positions FROM Employees e -- 1 LEFT JOIN Departments d ON e.DepartmentID=d.ID -- 2 LEFT JOIN Positions p ON e.PositionID=p.ID -- 3 

 SELECT e.ID, -- 1.   e.Name EmployeeName, -- 2.   p.Name PositionName, -- 3.   d.Name DepartmentName -- 4.   FROM Employees e LEFT JOIN Departments d ON e.DepartmentID=d.ID LEFT JOIN Positions p ON e.PositionID=p.ID 

 SELECT e.ID, -- 1.   e.Name EmployeeName, -- 2.   p.Name PositionName, -- 3.   d.Name DepartmentName -- 4.   FROM Employees e LEFT JOIN Departments d ON e.DepartmentID=d.ID LEFT JOIN Positions p ON e.PositionID=p.ID WHERE d.ID=3 --     ID  Departments AND p.ID=3 --     ID  Positions ORDER BY e.Name --     Name  Employees 

 SELECT [DISTINCT] _  * FROM  WHERE  ORDER BY _ 

 Employees e LEFT JOIN Departments d ON e.DepartmentID=d.ID LEFT JOIN Positions p ON e.PositionID=p.ID 

 SELECT e.ID, e.Name EmployeeName, p.Name PositionName, d.Name DepartmentName FROM /*  -  */ Employees e LEFT JOIN Departments d ON e.DepartmentID=d.ID LEFT JOIN Positions p ON e.PositionID=p.ID /*  -  */ WHERE d.ID=3 AND p.ID=3 ORDER BY e.Name 

 SELECT ISNULL(dep.Name,'') DepName, COUNT(DISTINCT emp.PositionID) PositionCount, COUNT(*) EmplCount, SUM(emp.Salary) SalaryAmount, AVG(emp.Salary) SalaryAvg --     FROM /*  -  */ Employees emp LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID /*  -  */ GROUP BY emp.DepartmentID,dep.Name ORDER BY DepName 

CROSS JOINを使用した有望な例

 SELECT d.Name DepartmentName, p.Name PositionName, e.EmplCount FROM Departments d CROSS JOIN Positions p LEFT JOIN ( /*           (DepartmentID,PositionID) */ SELECT DepartmentID,PositionID,COUNT(*) EmplCount FROM Employees GROUP BY DepartmentID,PositionID ) e ON e.DepartmentID=d.ID AND e.PositionID=p.ID ORDER BY DepartmentName,PositionName 

 SELECT DepartmentID,PositionID,COUNT(*) EmplCount FROM Employees GROUP BY DepartmentID,PositionID 

WHERE句を使用した通信

 SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp JOIN Departments dep ON emp.DepartmentID=dep.ID --    WHERE emp.DepartmentID=3 --    

 SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp, Departments dep WHERE emp.DepartmentID=dep.ID --    AND emp.DepartmentID=3 --    

 SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp CROSS JOIN Departments dep --   (  ) WHERE emp.DepartmentID=3 --    

 SELECT emp.ID,emp.Name,emp.DepartmentID,dep.ID,dep.Name FROM Employees emp, Departments dep WHERE emp.DepartmentID=3 --    

UNION結合-クエリ結果の垂直結合の操作

 SELECT '' Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID=1 --    SELECT '' Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID=2 --    SELECT '' Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID=3 --     SELECT '' Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID IS NULL --        

 SELECT '' Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID=1 --    UNION ALL SELECT '' Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID=2 --    UNION ALL SELECT '' Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID=3 --     UNION ALL SELECT '' Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID IS NULL --        

理論のビット

 CREATE TABLE TopTable( T1 int, T2 varchar(10) ) GO CREATE TABLE BottomTable( B1 int, B2 varchar(10) ) GO INSERT TopTable(T1,T2)VALUES (1,'Text 1'), (1,'Text 1'), (2,'Text 2'), (3,'Text 3'), (4,'Text 4'), (5,'Text 5') INSERT BottomTable(B1,B2)VALUES (2,'Text 2'), (3,'Text 3'), (6,'Text 6'), (6,'Text 6') 

 SELECT * FROM TopTable 

 SELECT * FROM BottomTable 

UNION ALL

 SELECT T1 x,T2 y FROM TopTable UNION ALL SELECT B1,B2 FROM BottomTable 

UNION

 SELECT T1 x,T2 y FROM TopTable UNION SELECT B1,B2 FROM BottomTable 

を除く

 SELECT T1 x,T2 y FROM TopTable EXCEPT SELECT B1,B2 FROM BottomTable 

交差

 SELECT T1 x,T2 y FROM TopTable INTERSECT SELECT B1,B2 FROM BottomTable 

UNION接続に関する会話を終了する

 CREATE TABLE NextTable( N1 int, N2 varchar(10) ) GO INSERT NextTable(N1,N2)VALUES (1,'Text 1'), (4,'Text 4'), (6,'Text 6') 

 SELECT T1 x,T2 y FROM TopTable EXCEPT SELECT B1,B2 FROM BottomTable INTERSECT SELECT N1,N2 FROM NextTable 

 ( SELECT T1 x,T2 y FROM TopTable EXCEPT SELECT B1,B2 FROM BottomTable ) INTERSECT SELECT N1,N2 FROM NextTable 

 SELECT x,y FROM ( SELECT T1 x,T2 y FROM TopTable EXCEPT SELECT B1,B2 FROM BottomTable ) q INTERSECT SELECT N1,N2 FROM NextTable 

 SELECT T1 x,T2 y FROM TopTable UNION ALL SELECT B1,B2 FROM BottomTable UNION ALL SELECT B1,B2 FROM BottomTable ORDER BY x DESC 

ご注意 Oracleにも同じ種類の接続がありますが、唯一の違いはEXCEPT操作で、MINUSと呼ばれます。

サブクエリを使用する

 SELECT q1.x1,q1.y1,q2.x2,q2.y2 FROM ( SELECT T1 x1,T2 y1 FROM TopTable EXCEPT SELECT B1,B2 FROM BottomTable ) q1 JOIN ( SELECT T1 x2,T2 y2 FROM TopTable EXCEPT SELECT N1,N2 FROM NextTable ) q2 ON q1.x1=q2.x2 

WITH句

 SELECT q1.x1,q1.y1,q2.x2,q2.y2 FROM ( SELECT T1 x1,T2 y1 FROM TopTable EXCEPT SELECT B1,B2 FROM BottomTable ) q1 JOIN ( SELECT T1 x2,T2 y2 FROM TopTable EXCEPT SELECT N1,N2 FROM NextTable ) q2 ON q1.x1=q2.x2 

 WITH q1 AS( SELECT T1 x1,T2 y1 FROM TopTable EXCEPT SELECT B1,B2 FROM BottomTable ), q2 AS( SELECT T1 x2,T2 y2 FROM TopTable EXCEPT SELECT N1,N2 FROM NextTable ) --      SELECT q1.x1,q1.y1,q2.x2,q2.y2 FROM q1 JOIN q2 ON q1.x1=q2.x2 

 CREATE VIEW ViewEmployeesInfo AS SELECT emp.*, --     Employees dep.Name DepartmentName, --      Name   Departments pos.Name PositionName --     Name   Positions FROM Employees emp LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID LEFT JOIN Positions pos ON emp.PositionID=pos.ID 

 SELECT DepartmentName, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount, AVG(Salary) SalaryAvg FROM ViewEmployeesInfo emp GROUP BY DepartmentID,DepartmentName ORDER BY DepartmentName 

 WITH cteEmployeesInfo AS( SELECT emp.*, --     Employees dep.Name DepartmentName, --      Name   Departments pos.Name PositionName --     Name   Positions FROM Employees emp LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID LEFT JOIN Positions pos ON emp.PositionID=pos.ID ) SELECT DepartmentName, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount, AVG(Salary) SalaryAvg FROM cteEmployeesInfo emp GROUP BY DepartmentID,DepartmentName ORDER BY DepartmentName 

WITHの使用はCTE式とも呼ば
れます。同じクエリに複数回アクセスする必要がある場合、Common Table Expressions（CTE）はコードの量を大幅に削減できます。CTEは、単一の要求の一部として作成され、スキーマオブジェクトとして保存されないビューの役割を果たします。

CTEには別の重要な目的があり、その助けを借りて、再帰クエリを作成できます。

 WITH cteEmpl AS( SELECT ID,CAST(Name AS nvarchar(300)) Name,1 EmpLevel FROM Employees WHERE ManagerID IS NULL --       UNION ALL SELECT emp.ID,CAST(SPACE(cte.EmpLevel*5)+emp.Name AS nvarchar(300)),cte.EmpLevel+1 FROM Employees emp JOIN cteEmpl cte ON emp.ManagerID=cte.ID ) SELECT * FROM cteEmpl 

サブクエリに関する会話を続ける

SELECTブロックでサブクエリを使用できます

 SELECT DepartmentID, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount, AVG(Salary) SalaryAvg --     FROM Employees GROUP BY DepartmentID 

 SELECT /*                 */ (SELECT Name FROM Departments dep WHERE dep.ID=emp.DepartmentID) DepartmentName, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount, AVG(Salary) SalaryAvg FROM Employees emp --   GROUP BY DepartmentID ORDER BY DepartmentName 

 SELECT ID, Name, --  1 -  ID  (SELECT MAX(ID) FROM Employees emp WHERE emp.DepartmentID=dep.ID) LastEmpID_var1, --  1 -  ID  (SELECT TOP 1 ID FROM Employees emp WHERE emp.DepartmentID=dep.ID ORDER BY ID DESC) LastEmpID_var2, --  2 -    (SELECT TOP 1 Name FROM Employees emp WHERE emp.DepartmentID=dep.ID ORDER BY ID DESC) LastEmpName FROM Departments dep 

サブクエリを適用する

 SELECT ID, Name, --  1 -  ID  (SELECT TOP 1 ID FROM Employees emp WHERE emp.DepartmentID=dep.ID ORDER BY ID DESC) LastEmpID, --  2 -    (SELECT TOP 1 Name FROM Employees emp WHERE emp.DepartmentID=dep.ID ORDER BY ID DESC) LastEmpName FROM Departments dep 

 SELECT ID, Name, empInfo.LastEmpID, empInfo.LastEmpName FROM Departments dep CROSS APPLY ( SELECT TOP 1 ID LastEmpID,Name LastEmpName FROM Employees emp WHERE emp.DepartmentID=dep.ID ORDER BY emp.ID DESC ) empInfo 

 SELECT ID, Name, empInfo.LastEmpID, empInfo.LastEmpName FROM Departments dep OUTER APPLY ( SELECT TOP 1 ID LastEmpID,Name LastEmpName FROM Employees emp WHERE emp.DepartmentID=dep.ID ORDER BY emp.ID DESC ) empInfo 

 SELECT dep.ID,dep.Name,pos.PositionID,pos.PositionName FROM Departments dep CROSS APPLY ( SELECT ID PositionID,Name PositionName FROM Positions ) pos 

WHEREブロックでのサブクエリの使用

 SELECT * FROM Departments dep WHERE (SELECT COUNT(*) FROM Employees emp WHERE emp.DepartmentID=dep.ID)>2 

EXISTSおよびNOT EXISTSコンストラクト

 --         SELECT * FROM Departments dep WHERE EXISTS(SELECT * FROM Employees emp WHERE emp.DepartmentID=dep.ID) 

 --        SELECT * FROM Departments dep WHERE NOT EXISTS(SELECT * FROM Employees emp WHERE emp.DepartmentID=dep.ID) 

サブクエリを使用したINおよびNOT IN構文

 --     SELECT * FROM Departments WHERE ID IN(SELECT DISTINCT DepartmentID FROM Employees WHERE DepartmentID IS NOT NULL) 

 --     SELECT * FROM Departments WHERE ID NOT IN(SELECT DISTINCT DepartmentID FROM Employees WHERE DepartmentID IS NOT NULL) 

グループ比較操作ALLおよびANY

 SELECT ID,Name,DepartmentID,Salary FROM Employees e1 WHERE e1.Salary>ALL( SELECT e2.Salary FROM Employees e2 WHERE e2.DepartmentID=e1.DepartmentID --       AND e2.ID<>e1.ID --        AND e2.Salary IS NOT NULL --  NULL  ) 

 SELECT ID,Name,DepartmentID,Salary FROM Employees e1 WHERE NOT EXISTS( SELECT * FROM Employees e2 WHERE e2.DepartmentID=e1.DepartmentID --       AND e2.Salary>e1.Salary --        ) 

 SELECT ID,Name,DepartmentID,Salary FROM Employees e1 WHERE e1.Salary>ANY( -- ANY = SOME SELECT e2.Salary FROM Employees e2 WHERE e2.DepartmentID=e1.DepartmentID --       AND e2.ID<>e1.ID --        ) 

 SELECT ID,Name,DepartmentID,Salary FROM Employees e1 WHERE EXISTS( SELECT * FROM Employees e2 WHERE e2.DepartmentID=e1.DepartmentID --       AND e2.Salary<e1.Salary --            ) 

 SELECT * FROM Departments WHERE ID=ANY(SELECT DISTINCT DepartmentID FROM Employees) 

 SELECT * FROM Departments WHERE ID IN(SELECT DISTINCT DepartmentID FROM Employees) 

 SELECT * FROM Employees e1 WHERE e1.Salary>ALL(SELECT e2.Salary FROM Employees e2 WHERE e2.ID<>e1.ID AND e2.Salary IS NOT NULL) 

 SELECT * FROM Employees e1 WHERE NOT EXISTS(SELECT * FROM Employees e2 WHERE e2.Salary>e1.Salary) 

サブクエリについてのもう少しの言葉

おわりに

• 以下を使用できるようにするOVER句。
  • GROUP BYを使用しない集計関数（COUNT、SUM、MIN、MAX、AVG）。
  • ランキング関数：ROW_NUMBER（）、RANK（）およびDENSE_RANK（）;
  • 分析関数：LAG（）およびLEAD（）、FIRST_VALUE（）およびLAST_VALUE（）;
• GROUP BY ROLLUP（...）、GROUP BY GROUPING SETS（...）、...およびこれらの目的で使用される補助関数を計算できる構造を研究するため：GROUPING_ID（）およびGROUPING（）;
• PIVOT、UNPIVOTを構築します。