SQL 写成 Linq 急 急 急 在线等

Question

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

十 万 火 急

Answer 1

木有调试，看看对不对 ( 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), } )

Answer 2

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

Answer 3

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

Answer 4

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)}) ); 大概写法就是这样，自己拿去套吧。