ROW_NUMBER() OVER (PARTITION BY) をLINQに変換したい
いつもお世話になってます。
下記のSQLと同じ結果をLINQで取り出したいのですが、期待値が取れずに苦戦しています。
こちらの情報をもとに作成をしてみたのですが、パーテーションが3つあるという点が違うのと、foreachで入れ子にしている部分で間違いが起きてるのかなと予想しているのですが、なかなか期待値が取れずにいます。
知りたい事。
LINQでのROW_NUMBER() OVER (PARTITION BY )で複合キーは可能なのか。
LEFT JOINした後でグループ化をSQLではしているので、その順番でやっているが正しいのかどうか？
foreachの入れ子は正しいのか。
です。
皆さま、ご教授いただけると幸いです。
テーブル定義
TMP1
ID int
START_DATE datetime
TOUROKU_NO int
CD string
TMP2
ID int
START_DATE datetime
TOUROKU_NO int
CD string
END_DATE datetime
SQL
SELECT
BB4.[ID],
BB4.[START_DATE],
BB4.[TOUROKU_NO],
MAX(CASE WHEN BB4.[RN] = 1 THEN BB4.[CD] ELSE '' END) AS [CD],
MAX(CASE WHEN BB4.[RN] = 1 THEN BB4.[END_DATE] ELSE '' END) AS [END_DATE]
FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY BB3.[ID], BB3.[START_DATE], BB3.[TOUROKU_NO] ORDER BY BB3.[END_DATE]) AS [RN]
FROM (
SELECT
BB1.*,
CASE WHEN BB2.[START_DATE] is NULL THEN GETDATE() ELSE BB2.[START_DATE] END AS [END_DATE]
FROM #TMP1 AS BB1
LEFT JOIN #TMP1 AS BB2 ON (BB1.[ID] = BB2.[ID] AND BB1.[TOUROKU_NO] = BB2.[TOUROKU_NO] AND BB1.[START_DATE] < BB2.[START_DATE])
) AS BB3
) AS BB4
GROUP BY BB4.[ID], BB4.[START_DATE], BB4.[TOUROKU_NO]
LINQ
//自己結合
var joinTable = from a in TMP1
join a2 in TMP1
on a.ID equals a2.ID into g
from a2 in g.DefaultIfEmpty()
where a.TOUROKU_NO == a2.TOUROKU_NO & a.START_DATE < a2.START_DATE
select new {
a.ID,
a.START_DATE,
a.TOUROKU_NO,
a.CD,
END_DATE = (DBNull.Value.Equals(a2.START_DATE) ? (DateTime)a2.SERVERDATE : (DateTime)a2.START_DATE)
};
//グループ化
var groupTable = from a in joinTable
group a by new { a.ID, a.START_DATE, a.TOUROKU_NO } into g
select new {
ID = from m in g
select m.ID,
START_DATE = g.Key.START_DATE,
TOUROKU_NO = g.Key.TOUROKU_NO,
CD = from m in g
select m.CD,
END_DATE = from m in g
orderby m.END_DATE
select m.END_DATE
};
foreach (var item in groupTable) {
foreach (var id in item.ID) {
foreach (var cd in item.CD) {
foreach (var end_date in item.END_DATE) {
var Row = DataTable.NewRow();
Row[0] = id.ToString();
Row[1] = DatePicker(item.START_DATE.ToString());
Row[2] = Convert.ToInt32(item.TOUROKU_NO);
Row[3] = cd.ToString();
Row[4] = DatePicker(end_date.ToString());
DataTable.Rows.Add(Row);
}
}
}
}
追記
SQL1
SELECT
BB1.*,
CASE WHEN BB2.[START_DATE] is NULL THEN GETDATE() ELSE BB2.[START_DATE] END AS [END_DATE]
FROM TMP1 AS BB1
LEFT JOIN TMP1 AS BB2 ON (BB1.[id] = BB2.[id] AND BB1.[TOUROKU_NO] = BB2.[TOUROKU_NO] AND BB1.[START_DATE] < BB2.[START_DATE])
SQL1の結果
ID STARTDATE TOROKU_NO CD END_DATE
7 2010-06-30 00:00:00.000 1 03010, 2011-07-05 00:00:00.000
7 2010-06-30 00:00.000 1 03010, 2011-09-26 00:00:00.000
7 2010-06-30 00:00:00.000 1 03010, 2011-12-06 00:00:00.000
7 2010-06-30 00:00:00.000 2 02010, 2011-12-06 00:00:00.000
7 2010-06-30 00:00:00.000 3 10010, 2016-11-01 14:48:08.410
7 2010-06-30 00:00:00.000 4 04441, 2016-11-01 14:48:08.410
7 2011-07-05 00:00:00.000 1 09010, 2011-09-26 00:00:00.000
7 2011-07-05 00:00:00.000 1 09010, 2011-12-06 00:00:00.000
7 2011-09-26 00:00:00.000 1 10010, 2011-12-06 00:00:00.000
7 2011-12-06 00:00:00.000 1 09010, 2016-11-01 14:48:08.410
7 2011-12-06 00:00:00.000 2 04441, 2016-11-01 14:48:08.410
10 2010-04-28 00:00:00.000 1 02310, 2012-01-23 00:00:00.000
10 2010-04-28 00:00:00.000 1 02310, 2010-10-25 00:00:00.000
10 2010-04-28 00:00:00.000 1 02310, 2011-02-07 00:00:00.000
10 2010-04-28 00:00:00.000 1 02310, 2011-10-12 00:00:00.000
10 2010-10-25 00:00:00.000 1 02310, 2012-01-23 00:00:00.000
LINQ1
SQL1をlinqに変換
var joinTable = from a in TMP1
join a2 in TMP1
on a.ID equals a2.ID into g
from a2 in g.DefaultIfEmpty()
where a.TOUROKU_NO == a2.TOUROKU_NO & a.START_DATE < a2.START_DATE
select new {
a.ID,
a.START_DATE,
a.TOUROKU_NO,
a.CD,
END_DATE = (DBNull.Value.Equals(a2.START_DATE) ? (DateTime)a2.SERVERDATE : (DateTime)a2.START_DATE)
};
foreach (var item in joinTable) {
ID = item.ID.ToString();
START_DATE = item.START_DATE;
TOUROKU_NO = item.TOUROKU_NO;
CD = item.CD.ToString();
END_DATE = item.END_DATE;
}
LINQ1の結果
ID STARTDATE TOROKU_NO CD END_DATE
7 2010/06/30 0:00:00 1 03010, 2011/07/05 0:00:00
7 2010/06/30 0:00:00 1 03010, 2011/09/26 0:00:00
7 2010/06/30 0:00:00 1 03010, 2011/12/06 0:00:00
7 2010/06/30 0:00:00 2 02010, 2011/12/06 0:00:00
7 2011/07/05 0:00:00 1 09010, 2011/09/26 0:00:00
7 2011/07/05 0:00:00 1 09010, 2011/12/06 0:00:00
7 2011/09/26 0:00:00 1 10010, 2011/12/06 0:00:00
10 2010/04/28 0:00:00 1 02310, 2010/10/25 0:00:00
10 2010/04/28 0:00:00 1 02310, 2011/02/07 0:00:00
10 2010/04/28 0:00:00 1 02310, 2011/10/12 0:00:00
10 2010/04/28 0:00:00 1 02310, 2012/01/23 0:00:00
10 2010/10/25 0:00:00 1 02310, 2011/02/07 0:00:00
10 2010/10/25 0:00:00 1 02310, 2011/10/12 0:00:00
10 2010/10/25 0:00:00 1 02310, 2012/01/23 0:00:00
10 2011/02/07 0:00:00 1 02310, 2011/10/12 0:00:00
10 2011/02/07 0:00:00 1 02310, 2012/01/23 0:00:00
leftjoinで自己結合したいのですが、上記のSQLの結果のようにいきませんでした。
いかようにlinqを書けばSQL１と同じ値になるのでしょうか？
SQL2
SQL2
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY BB3.[id], BB3.[START_DATE], BB3.[TOUROKU_NO] ORDER BY BB3.[END_DATE]) AS [RN]
FROM (
SELECT
BB1.*,
CASE WHEN BB2.[START_DATE] is NULL THEN GETDATE() ELSE BB2.[START_DATE] END AS [END_DATE]
FROM TMP1 AS BB1
LEFT JOIN TMP1 AS BB2 ON (BB1.[id] = BB2.[id] AND BB1.[TOUROKU_NO] = BB2.[TOUROKU_NO] AND BB1.[START_DATE] < BB2.[START_DATE])
) AS BB3
SQL2の結果
ID STARTDATE TOROKU_NO CD END_DATE RN
7 2010-06-30 00:00:00.000 1 03008, 2011-07-05 00:00:00.000 1
7 2010-06-30 00:00:00.000 1 03009, 2011-09-26 00:00:00.000 2
7 2010-06-30 00:00:00.000 1 03009, 2011-12-06 00:00:00.000 3
7 2010-06-30 00:00:00.000 2 02009, 2011-12-06 00:00:00.000 1
7 2010-06-30 00:00:00.000 3 10009, 2016-11-01 14:50:38.060 1
7 2010-06-30 00:00:00.000 4 04440, 2016-11-01 14:50:38.060 1
7 2011-07-05 00:00:00.000 1 09010, 2011-09-26 00:00:00.000 1
7 2011-07-05 00:00:00.000 1 09010, 2011-12-06 00:00:00.000 2
7 2011-09-26 00:00:00.000 1 10010, 2011-12-06 00:00:00.000 1
7 2011-12-06 00:00:00.000 1 09010, 2016-11-01 14:50:38.060 1
7 2011-12-06 00:00:00.000 2 04441, 2016-11-01 14:50:38.060 1
10 2010-04-28 00:00:00.000 1 02310, 2010-10-25 00:00:00.000 1
10 2010-04-28 00:00:00.000 1 02310, 2011-02-07 00:00:00.000 2
10 2010-04-28 00:00:00.000 1 02310, 2011-10-12 00:00:00.000 3
10 2010-04-28 00:00:00.000 1 02310, 2012-01-23 00:00:00.000 4
10 2010-10-25 00:00:00.000 1 02310, 2011-02-07 00:00:00.000 1
10 2010-10-25 00:00:00.000 1 02310, 2011-10-12 00:00:00.000 2
10 2010-10-25 00:00:00.000 1 02310, 2012-01-23 00:00:00.000 3
10 2011-02-07 00:00:00.000 1 02310, 2011-10-12 00:00:00.000 1
10 2011-02-07 00:00:00.000 1 02310, 2012-01-23 00:00:00.000 2
10 2011-02-07 00:00:00.000 2 02310,03020,18010, 2016-11-01 14:50:38.060 1
10 2011-02-07 00:00:00.000 3 20010,02030, 2016-11-01 14:50:38.060 1
10 2011-02-07 00:00:00.000 4 01210, 2016-11-01 14:50:38.060 1
10 2011-02-07 00:00:00.000 5 02010, 2016-11-01 14:50:38.060 1
10 2011-02-07 00:00:00.000 6 20010, 2016-11-01 14:50:38.060 1
10 2011-02-07 00:00:00.000 7 02030, 2016-11-01 14:50:38.060 1
10 2011-10-12 00:00:00.000 1 02310, 2012-01-23 00:00:00.000 1
10 2012-01-23 00:00:00.000 1 02310, 2016-11-01 14:50:38.060 1
11 2010-04-28 00:00:00.000 1 02310, 2010-10-25 00:00:00.000 1
11 2010-04-28 00:00:00.000 1 02310, 2011-02-07 00:00:00.000 2
11 2010-04-28 00:00:00.000 1 02310, 2011-06-27 00:00:00.000 3
11 2010-04-28 00:00:00.000 1 02310, 2011-10-12 00:00:00.000 4
11 2010-04-28 00:00:00.000 1 02310, 2012-01-23 00:00:00.000 5
11 2010-10-25 00:00:00.000 1 02310, 2011-02-07 00:00:00.000 1
11 2010-10-25 00:00:00.000 1 02310, 2011-06-27 00:00:00.000 2
SQL3
SQL3
SELECT
BB4.[id],
BB4.[START_DATE],
BB4.[TOUROKU_NO],
MAX(CASE WHEN BB4.[RN] = 1 THEN BB4.[CD] ELSE '' END) AS [CD],
MAX(CASE WHEN BB4.[RN] = 1 THEN BB4.[END_DATE] ELSE '' END) AS [END_DATE]
From(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY BB3.[id], BB3.[START_DATE], BB3.[TOUROKU_NO] ORDER BY BB3.[END_DATE]) AS [RN]
FROM (
SELECT
BB1.*,
CASE WHEN BB2.[START_DATE] is NULL THEN GETDATE() ELSE BB2.[START_DATE] END AS [END_DATE]
FROM TMP1 AS BB1
LEFT JOIN TMP1 AS BB2 ON (BB1.[id] = BB2.[id] AND BB1.[TOUROKU_NO] = BB2.[TOUROKU_NO] AND BB1.[START_DATE] < BB2.[START_DATE])
) AS BB3
) AS BB4
GROUP BY BB4.[id], BB4.[START_DATE], BB4.[TOUROKU_NO]
SQL3の結果
こちらの結果が最終的に取得したいデータです。
ID STARTDATE TOROKU_NO CD END_DATE
7 2010-06-30 00:00:00.000 1 03010, 2011-07-05 00:00:00.000
7 2010-06-30 00:00:00.000 2 02010, 2011-12-06 00:00:00.000
7 2010-06-30 00:00:00.000 3 10010, 2016-11-01 14:53:38.593
7 2010-06-30 00:00:00.000 4 04441, 2016-11-01 14:53:38.593
7 2011-07-05 00:00:00.000 1 09010, 2011-09-26 00:00:00.000
7 2011-09-26 00:00:00.000 1 10010, 2011-12-06 00:00:00.000
7 2011-12-06 00:00:00.000 1 09010, 2016-11-01 14:53:38.593
7 2011-12-06 00:00:00.000 2 04441, 2016-11-01 14:53:38.593
10 2010-04-28 00:00:00.000 1 02310, 2010-10-25 00:00:00.000
10 2010-10-25 00:00:00.000 1 02310, 2011-02-07 00:00:00.000
10 2011-02-07 00:00:00.000 1 02310, 2011-10-12 00:00:00.000
10 2011-02-07 00:00:00.000 2 02310,03020,18010, 2016-11-01 14:53:38.593
10 2011-02-07 00:00:00.000 3 20010,02030, 2016-11-01 14:53:38.593
10 2011-02-07 00:00:00.000 4 01210, 2016-11-01 14:53:38.593
10 2011-02-07 00:00:00.000 5 02010, 2016-11-01 14:53:38.593
10 2011-02-07 00:00:00.000 6 20010, 2016-11-01 14:53:38.593
10 2011-02-07 00:00:00.000 7 02030, 2016-11-01 14:53:38.593
10 2011-10-12 00:00:00.000 1 02310, 2012-01-23 00:00:00.000
10 2012-01-23 00:00:00.000 1 02310, 2016-11-01 14:53:38.593
11 2010-04-28 00:00:00.000 1 02310, 2010-10-25 00:00:00.000
11 2010-10-25 00:00:00.000 1 02310, 2011-02-07 00:00:00.000
11 2011-02-07 00:00:00.000 1 02310, 2011-06-27 00:00:00.000
11 2011-06-27 00:00:00.000 1 02310, 2011-10-12 00:00:00.000
11 2011-10-12 00:00:00.000 1 02310, 2012-01-23 00:00:00.000
皆さまなにとぞご教授の程よろしくお願いします。