サブクエリは前からある機能ですが、おさらいで。
これも、クエリを先に作っておいて、Castで文中に入れ込みます。入れたところで展開されます。
Select句
public void SelectSubQuery() { //moneyの全合計 var sub = Db<DB>.Sql(db=> Select(new { total = Sum(db.tbl_remuneration.money) }). From(db.tbl_remuneration)); //スタッフごとにグルーピングされたmoneyと全体の合計を同時に表示する var query = Db<DB>.Sql(db => Select(new { name = db.tbl_staff.name, personalTotal = Sum(db.tbl_remuneration.money), total = sub.Cast<decimal>()//★サブクエリ 代入したかのようなコードが書ける }). From(db.tbl_staff). Join(db.tbl_remuneration, db.tbl_remuneration.staff_id == db.tbl_staff.id). GroupBy(db.tbl_remuneration.staff_id, db.tbl_staff.name)); //文字列化 Debug.Print(query.Build(typeof(SqlConnection)).Text); //Dapperを使っているなら、以下のように実行できます var datas = _connection.Query(query).ToList(); }
SELECT tbl_staff.name AS name, SUM(tbl_remuneration.money) AS personalTotal, (SELECT SUM(tbl_remuneration.money) AS total FROM tbl_remuneration) AS total FROM tbl_staff JOIN tbl_remuneration ON (tbl_remuneration.staff_id) = (tbl_staff.id) GROUP BY tbl_remuneration.staff_id, tbl_staff.name
Where句
public void WhereInSubQuery() { //サブクエリ作成 var sub = Db<DB>.Sql(db => Select(new { money = db.tbl_remuneration.money }). From(db.tbl_remuneration). Join(db.tbl_staff, db => db.tbl_remuneration.staff_id == db.tbl_staff.id). Where(3000 < db.tbl_remuneration.money && db.tbl_remuneration.money < 4000)); //Inにサブクエリを入れる var query = Sql<Data>.Create(db => Select(new Asterisk()). From(db.tbl_remuneration). //条件指定 Where(In(db.tbl_remuneration.money, sub.Cast<int>())); //文字列化 Debug.Print(query.Build(typeof(SqlConnection)).Text); //Dapperを使っているなら、以下のように実行できます var datas = _connection.Query(query).ToList(); }
SELECT * FROM tbl_remuneration WHERE tbl_remuneration.money IN( (SELECT tbl_remuneration.money AS money FROM tbl_remuneration JOIN tbl_staff ON (tbl_remuneration.staff_id) = (tbl_staff.id) WHERE ((@p_0) < (tbl_remuneration.money)) AND ((tbl_remuneration.money) < (@p_1))))
From句
From句で使うときは少しコツがあって、DB定義の部分で使っておきます。ここに入ってないと、続くクエリ構築で使えないのです。その後、From句で(JoinでもOK)で使います。
public void FromSubQuery() { //サブクエリ作成 var sub = Db<DB>.Sql(db => Select(new { name = db.tbl_staff.name, payment_date = db.tbl_remuneration.payment_date, money = db.tbl_remuneration.money, }). From(db.tbl_remuneration). Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id). Where(3000 < db.tbl_remuneration.money && db.tbl_remuneration.money < 4000)); var query = Db<DB>.Sql(db => Select(new { name = sub.Body.name }). //From句でサブクエリのテーブル(的なもの)を指定 //View的な使い方 From(sub)); //文字列化 Debug.Print(query.Build(typeof(SqlConnection)).Text); //Dapperを使っているなら、以下のように実行できます var datas = _connection.Query(query).ToList(); }
SELECT sub.name AS name FROM (SELECT tbl_staff.name AS name, tbl_remuneration.payment_date AS payment_date, tbl_remuneration.money AS money FROM tbl_remuneration JOIN tbl_staff ON (tbl_remuneration.staff_id) = (tbl_staff.id) WHERE ((@p_2) < (tbl_remuneration.money)) AND ((tbl_remuneration.money) < (@p_3))) sub
履歴
2016/09/02 β版対応