LambdicSql - 機能紹介 - サブクエリ - ささいなことですが。

前回も少し書きましたが、LambdicSqlはサブクエリを書けます。

今回の例もこんなDBが対象です。

//テーブルが二つあるDB
public class DB
{
    public Staff tbl_staff { get; set; }
    public Remuneration tbl_remuneration { get; set; }
}
//スタッフテーブル
public class Staff
{
    public int id { get; set; }
    public string name { get; set; }
}
//報酬テーブル
public class Remuneration
{
    public int id { get; set; }
    public int staff_id { get; set; }
    public DateTime payment_date { get; set; }
    public decimal money { get; set; }
}

前回の例　Where句で利用

前回の例を再度書きます。でも、Cast<decima>()ってなんやねんって感じですよね。これは、色んなところでサブクエリ書けるようにするための仕様なんですよ。

public void WhereTest()
{
    //サブクエリ作成
    var sub = Sql<Data>.Create(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.ToSqlInfo(typeof(SqlConnection)).SqlConnection);

    //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))))

Select句で利用

Select句での利用を見てもらえば納得してもらえるかも。サブクエリの結果をdecimalで受けたいわけなのですが、普通に受けると、queryの型になってしまいますよね。そこでキャストです。指定された型を戻り値にとるので、この例ではdecimalで変数に代入したかのようなコードを書くことができます。実際にはこのコードは動くわけではなく、式木が解析されてSQLの文字列が作成されるだけなのですよ。あ、キャストはExpression内で使わないと変換されませんよw

//moneyの全合計
var sub = Sql<DB>.Create(db=>
    Select(new
    {
        total = Sum(db.tbl_remuneration.money)
    }).
    From(db.tbl_remuneration));

//スタッフごとにグルーピングされたmoneyと全体の合計を同時に表示する
var query = Sql<DB>.Create(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.ToSqlInfo(typeof(SqlConnection)).SqlText);

//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

From句で利用

From句でも利用できます。ちょっと普通のSQLとは書き方異なってしまいます。

public void SubQuery2()
{
    //サブクエリ作成
    var sub = Sql<DB>.Create(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 = Sql<DB>.Create(db =>
        Select(new
        {
            name = sub.Body.name
        }).
        //From句でサブクエリのテーブル（的なもの）を指定
        //View的な使い方
        From(sub));

    //文字列化
    Debug.Print(query.ToSqlInfo(typeof(SqlConnection)).SqlText);

    //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

この場合は、Viewを使うイメージで使ってもらうことになります。最初に利用するテーブルのようなイメージでQuery関数で指定してもらいます。この例では匿名クラスでやっていますが、もちろん普通の型でも同様のことができます。Generic指定なしのCast()を使うとそのクエリでSelectされている型になります。

他にもいろんなところで使える（はず

結局文字列置換です。他にも普通にサブクエリ書けるところでは使えると思います。

コード修正しました。

2016/07/09 SQL修正プリペアド対応
2016/09/02 β版対応

前回の例 Where句で利用

Select句で利用

From句で利用

他にもいろんなところで使える（はず

コード修正しました。

前回の例　Where句で利用