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()
{
var 人数1 = Sql<DB>.Create(db =>
Select(new
{
ID = db.友達.人ID1,
人数 = Count<int>(new Asterisk())
}).
From(db.友達).
GroupBy(db.友達.人ID1));
var 人数2 = Sql<DB>.Create(db =>
Select(new
{
ID = db.友達.人ID2,
人数 = Count<int>(new Asterisk())
}).
From(db.友達).
GroupBy(db.友達.人ID2));
var 友達一次元 = 人数1.Union(人数2);
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)
);
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)