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)})
 );

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