首页 新闻 搜索 专区 学院

sql变成linq:row_number() over partition by order by case when then

0
悬赏园豆:200 [已关闭问题] 关闭于 2013-09-27 12:52

sql:

select bond_id,bond_code, row_number() over(partition by bond_id order by 
 case sec_mar_par when 3 then 1
     when 1 then 2 when 2 then 3 when 4 then 4 else 5 end) no  
    from t

数据大概这个样子:

bond_id    bond_code    sec_mar_par
103005860    000001    3
103005862    000002    3
103005866    000003    3
103005868    000005    3
103005870    000006    3
103005872    000007    3
103005874    000008    3
103005876    000009    3
103005880    000011    3
103005882    000012    3
103005928    000696    2
103005928    0696    3
103005933    000896    2
103005943    009704    2
103005945    009908    2
103005947    010001    3
103005949    010002    3
103005951    010004    2
103005953    010004    3
103005955    010005    3

想要用linq查出来:

bond_id    bond_code    sec_mar_par    no
103005860    000001    3    1
103005862    000002    3    1
103005866    000003    3    1
103005868    000005    3    1
103005870    000006    3    1
103005872    000007    3    1
103005874    000008    3    1
103005876    000009    3    1
103005880    000011    3    1
103005882    000012    3    1
103005928    0696    3    1
103005928    000696    2    2
103005933    000896    2    1
103005943    009704    2    1
103005945    009908    2    1
103005947    010001    3    1
103005949    010002    3    1
103005951    010004    2    1
103005953    010004    3    1
103005955    010005    3    1

s那个字段有3的话,3的order是1,没3的话,2的order是1

然后在order上加上rownumber

就这样

sql会写,但是linq不会,请大神来帮忙

sheng00的主页 sheng00 | 菜鸟二级 | 园豆:324
提问于:2013-09-27 12:21
< >
分享
所有回答(2)
0
var q = query.AsEnumerable()
                        .OrderBy(x => switchOrder(x.SEC_MAR_PAR))
                        .GroupBy(x => x.BOND_CODE)
                        .Select(g => new { g, count = g.Count() })
                        .SelectMany(t => t.g.Select(b => b)
                        .Zip(Enumerable.Range(1, t.count), (j, i) => new { j.BOND_ID, j.BOND_CODE, rn = i }));
sheng00 | 园豆:324 (菜鸟二级) | 2013-09-27 12:51
0

帮顶

飞无痕落无声 | 园豆:209 (菜鸟二级) | 2013-09-27 13:39
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册