如何把下面的执行查询的sql语句转化为linq to sql语句？
建测试环境：
create table a(YearMonth char(6),Number numeric(10,2),CodeA char(1))
insert a select '200501', 21.12, 'A'
insert a select '200501', 23.12, 'B'
insert a select '200502', 26.12, 'A'
insert a select '200502', 27.12, 'B'
insert a select '200502', 30.12, 'C'
执行查询：
declare @sql varchar(6000)
set @sql = 'select YearMonth,'
select @sql = @sql +' ['+ CodeA +']=sum(case CodeA when '''+CodeA +''' then Number else NULL end),'
from a group by CodeA order by CodeA
set @sql = left(@sql,len(@sql)-1)+' from a group by YearMonth '
exec(@sql)
我在SQL里面测试了一下，@sql生成的sql语句是
select YearMonth, [A]=sum(case CodeA when 'A' then Number else NULL end), [B]=sum(case CodeA when 'B' then Number else NULL end), [C]=sum(case CodeA when 'C' then Number else NULL end) from a group by YearMonth而执行结果是
也就是按月份算出A、B、C的合计
而a表的数据是
那么linq的写法
class A{
public String YearMonth;
public double Number;
public String CodeA;
}
List<A> aList;//从数据库中读出的A表所有行
var query = aList.GroupBy(q => q.YearMonth)
.Select(q => new
{
YearMonth = q.First().YearMonth,
A = q.Where(s => s.CodeA == "A").Sum(s => s.Number),
B = q.Where(s => s.CodeA == "B").Sum(s => s.Number),
C = q.Where(s => s.CodeA == "C").Sum(s => s.Number)
});
foreach (var queryResult in query)
{
Console.WriteLine(queryResult.YearMonth + " " queryResult.A + " " + queryResult.B + " " + queryResult.C);
}
Console.ReadLine();
执行结果：
十分感谢你的回答。
但是如果只是这样那就好做了，问题是查询结果的列数是不确定的！
换句话说，最初始那张表中第三列里会有D、E，也会因为需要删除A、B、C中的一项或几项，这样的话linq语句里就不能把select里的内容写死。
我给的那段sql插入数据代码第三列在添加了'D''E'这两项后，查询结果也会显示D、E这两列。
哦，原来你是要这样的效果啊，那改成这样好了
var query = aList.GroupBy(q => q.YearMonth).Select(q => new{
YearMonth = q.First().YearMonth,
Sums = q.GroupBy(s => s.CodeA)
.Select(s => new { CodeA = s.First().CodeA, Sum = s.Sum(c => c.Number) })
});
foreach (var queryResult in query)
{
String s = queryResult.YearMonth;
foreach (var sum in queryResult.Sums)
{
s += " " + sum.CodeA + ":" + sum.Sum;
}
Console.WriteLine(s);
}
执行结果是这样的
