首页 新闻 会员 周边

SQL 写成 Linq 急 急 急 在线等

0
悬赏园豆：100 [已解决问题] 解决于 2014-09-10 17:45 
select 
    t1.value0 as 'External Services',
    t1.value1 as 'SST MRR ( 万人民币 )',
    t1.value2 as 'SST ACV (万人民币)',
    t1.value3 as 'Deduct TCV (万人民币)',
    t1.value4 as 'SST MRR ( 万人民币 )',
    t1.value5 as 'Total'
from
(   select 
        [External Services] as value0,
        sum([SST MRR ( 万人民币 )]) as value1,
        sum([SST ACV (万人民币)]) as value2,
        sum([Deduct TCV (万人民币)]) as value3,
        SUM([Deduct ACV (万人民币)]) as value4,
        sum([SST MRR ( 万人民币 )])+
        sum([SST ACV (万人民币)])+
        sum([Deduct TCV (万人民币)])+
        SUM([Deduct ACV (万人民币)]) as value5
    from 
        [Master] 
    where 
        [Record Year]=2014   
    group by 
        [External Services]
) as t1
union select 
    'Total',
    SUM(t1.value1),
    SUM(t1.value2),
    SUM(t1.value3),
    SUM(t1.value4),
    SUM(t1.value5)
from(select 
        [External Services] as value0,
        sum([SST MRR ( 万人民币 )]) as value1,
        sum([SST ACV (万人民币)]) as value2,
        sum([Deduct TCV (万人民币)]) as value3,
        SUM([Deduct ACV (万人民币)]) as value4,
        sum([SST MRR ( 万人民币 )])+
        sum([SST ACV (万人民币)])+
        sum([Deduct TCV (万人民币)])+
        SUM([Deduct ACV (万人民币)]) as value5
    from 
        [Master] 
    where 
        [Record Year]=2014   
    group by 
        [External Services] ) as t1

十 万 火 急

Net泶习的主页 Net泶习 | 初学一级 | 园豆：59
提问于：2014-06-18 17:18
< >
分享
最佳答案
0

木有调试，看看对不对

(
    from p in [Master] 
    where p.[Record Year]=2014  
    group p by p.[External Services] into g
    select new
    {
        value0 = g.Key,
        value1 = g.Sum(p => p.[SST MRR ( 万人民币 )]),
        value2 = g.Sum(p => p.[SST ACV ( 万人民币 )]),
        value3 = g.Sum(p => p.[Deduct TCV ( 万人民币 )]),
        value4 = g.Sum(p => p.[Deduct ACV ( 万人民币 )]),
        value5 = g.Sum(p => p.[SST MRR ( 万人民币 )]) + 
        g.Sum(p => p.[SST ACV ( 万人民币 )]) + 
        g.Sum(p => p.[Deduct TCV ( 万人民币 )]) + 
        g.Sum(p => p.[Deduct ACV ( 万人民币 )]),
    }
).Union
(
    from t in
    (
        from p in [Master] 
        where p.[Record Year]=2014  
        group p by p.[External Services] into g
        select new
        {
            value0 = g.Key,
            value1 = g.Sum(p => p.[SST MRR ( 万人民币 )]),
            value2 = g.Sum(p => p.[SST ACV ( 万人民币 )]),
            value3 = g.Sum(p => p.[Deduct TCV ( 万人民币 )]),
            value4 = g.Sum(p => p.[Deduct ACV ( 万人民币 )]),
            value5 = g.Sum(p => p.[SST MRR ( 万人民币 )]) + 
            g.Sum(p => p.[SST ACV ( 万人民币 )]) + 
            g.Sum(p => p.[Deduct TCV ( 万人民币 )]) + 
            g.Sum(p => p.[Deduct ACV ( 万人民币 )]),
        }
    )
    group t by 1 into g
    select new
    {
        value0 = 'Total',
        value1 = g.Sum(t => t.value1),
        value2 = g.Sum(t => t.value2),
        value3 = g.Sum(t => t.value3),
        value4 = g.Sum(t => t.value4),
        value5 = g.Sum(t => t.value5),
    }
)
收获园豆：70
imyang | 菜鸟二级 |园豆：272 | 2014-06-19 15:34
其他回答(3)
0

最后弄个bak出来，如果没看错的话你这个弄成linq的话，行数最少翻3倍

邵明瑞 | 园豆：105 (初学一级) | 2014-06-18 18:54
0

会写SQL就会写linq

你这个东西别的不多说.字段名中间带空格的``你实体对象怎么定义?

吴瑞祥 | 园豆：29449 (高人七级) | 2014-06-19 09:46
0 
 db.Master.Where(x = > x.RecordYear == 2014).GroupBy(x = > x.ExternalServices)
 .Select(g = > new { value0=g.Key, value1 = g.Sum(x1 = > x1.SSTMRR),value2=g.Sum(x1=>x1.SSTACV)}).Union(
     db.Master.Where(x = > x.RecordYear == 2014).GroupBy(x = > x.ExternalServices)
     .Select(g = > new { value0=g.Key, value1 = g.Sum(x1 = > x1.SSTMRR),value2=g.Sum(x1=>x1.SSTACV)})
 );

大概写法就是这样，自己拿去套吧。

收获园豆：30
幻天芒 | 园豆：37205 (高人七级) | 2014-06-19 09:59
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。