SQLWorld★大阪#38 に参加してきました。

SQL力を高めるべく、SqlWorld :: SQLWorld★大阪#38に参加してきました！
簡単なものから高度すぎるものまで、レンジの広い問題が出題されました。
で、その中でこんな問題がありました。
友達の人数を抽出するというものです。

で、私はこんなSQLを書きました。

SELECT 人.名前,
       isnull(友達人数.人数, 0) AS 人数
FROM   人
       LEFT OUTER JOIN
       (SELECT   友達一次元.ID AS ID,
                 sum(人数) AS 人数
        FROM     (SELECT   人ID1 AS ID,
                           count(*) AS 人数
                  FROM     友達
                  GROUP BY 人ID1
                  UNION
                  SELECT   人ID2 AS ID,
                           count(*) AS 人数
                  FROM     友達
                  GROUP BY 人ID2) AS 友達一次元
        GROUP BY 友達一次元.ID) AS 友達人数
       ON 人.ID = 友達人数.ID;

LambdicSqlで書いてみます

まあ、サブクエリの入れ子とか複雑ですよね。これをLambdicSqlで書いてみようと思います。（実はもっと良い書き方を主催のおださんから教えていただいたのですが、LambdicSqlの題材としてはこっちの方がよかったので）
LambdicSqlは順に組み立てられるのでこんなとき便利です。
最後のToSqlInfoでがSQL文字列が作成されます。

//テーブルの定義
class 人
{
    public int ID { get; set; }
    public string 名前 { get; set; }
}
class 友達
{
    public int 人ID1 { get; set; }
    public int 人ID2 { get; set; }
}
class DB
{
    public 人 人 { get; set; }
    public 友達 友達 { get; set; }
}

[TestMethod]
public void TestSqlWorld()
{
    //①友達テーブルの人ID1の列で友達の数を数えるクエリ
    var 人数1 = Sql<DB>.Create(db =>
        Select(new
        {
            ID = db.友達.人ID1,
            人数 = Count<int>(new Asterisk())
        }).
        From(db.友達).
        GroupBy(db.友達.人ID1));

    //②友達テーブルの人ID2の列で友達の数を数えるクエリ
    var 人数2 = Sql<DB>.Create(db =>
        Select(new
        {
            ID = db.友達.人ID2,
            人数 = Count<int>(new Asterisk())
        }).
        From(db.友達).
        GroupBy(db.友達.人ID2));

    //③合算して一列のテーブルにするクエリ
    var 友達一次元 = 人数1.Union(人数2);

    //④IDでグルーピングして人数を数える
    var 友達人数 = Sql<DB>.Create(db =>
    Select(new
    {
        ID = 友達一次元.Body.ID,
        人数 = Sum(友達一次元.Body.人数)
    }).
    From(友達一次元).
    GroupBy(友達一次元.Body.ID));

    //⑤クエリ完成
    //名前を付けるクエリ
    var 友達人数_名前付き = Sql<DB>.Create(db =>
        Select(new
        {
            名前 = db.人.名前,
            人数 = IsNull(友達人数.Body.人数, 0)
        }).
        From(db.人).
        LeftJoin(友達人数, db.人.ID == 友達人数.Body.ID)
    );

    //文字列とパラメータの取得
    //後はDapperへ
    var info = 友達人数_名前付き.ToSqlInfo(typeof(SqlConnection));
    Debug.Print(info.SqlText);
}

こんなSQLになります。

SELECT
	人.名前 AS 名前,
	ISNULL(友達人数.人数, @p_1) AS 人数
FROM 人
	LEFT JOIN 
	(SELECT
		友達一次元.ID AS ID,
		SUM(友達一次元.人数) AS 人数
	FROM 
		(SELECT
			友達.人ID1 AS ID,
			COUNT(*) AS 人数
		FROM 友達
		GROUP BY 友達.人ID1
		UNION
		SELECT
			友達.人ID2 AS ID,
			COUNT(*) AS 人数
		FROM 友達
		GROUP BY 友達.人ID2) 友達一次元
	GROUP BY 友達一次元.ID) 友達人数 ON (人.ID) = (友達人数.ID)

履歴

2016/09/02 β版対応