lambdicSql - Queryの自由な組み立て - ③サブクエリ -

サブクエリは前からある機能ですが、おさらいで。
これも、クエリを先に作っておいて、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