我有一个SQL语句如下:
SELECT yearmonth, fenlei, pinhao, SUM(jine) AS jine,
SUM(qichujine) AS qichujine
FROM vw_cuihun
WHERE (pinhao = @pinhao) AND (yearmonth between @startdate and @enddate) AND qichukuc> 0 AND qichujine > 0
GROUP BY month,fenlei,pinhao
ORDER BY month,fenlei,pinhao 我想把他转化成用Linq to sql的语句,我这样写:DataClasses1DataContext date = new DataClasses1DataContext();
var q = from v in date.vw_cuihun
where (v.pinhao = 1001 && yearmonth < "2010-09-1" && v.yearmonth > "2010-01-1" && v.qichukuc>0)
select new
{
v.yearmonth,
}写到这里就写不下去了,我不知道用SUM和其它的列怎样同时列出来了,请高手帮我看一下应该怎样做,谢谢了啊~~
SELECT yearmonth, fenlei, pinhao, SUM(jine) AS jine,
SUM(qichujine) AS qichujine
FROM vw_cuihun
WHERE (pinhao = @pinhao) AND (yearmonth between @startdate and @enddate) AND qichukuc> 0 AND qichujine > 0
GROUP BY month,fenlei,pinhao
ORDER BY month,fenlei,pinhao 我想把他转化成用Linq to sql的语句,我这样写:DataClasses1DataContext date = new DataClasses1DataContext();
var q = from v in date.vw_cuihun
where (v.pinhao = 1001 && yearmonth < "2010-09-1" && v.yearmonth > "2010-01-1" && v.qichukuc>0)
select new
{
v.yearmonth,
}写到这里就写不下去了,我不知道用SUM和其它的列怎样同时列出来了,请高手帮我看一下应该怎样做,谢谢了啊~~
var q = from v in date.vw_cuihun
group v by new {v.month,v.fenlei,v.pinhao} into x
order by new{v.month,v.feilei,v.pinhao}
where (v.pinhao = 1001 && yearmonth < "2010-09-1" && v.yearmonth > "2010-01-1" && v.qichukuc>0)
select new
{
v.yearmonth,
Jine=x.Sum(y=>y.jine),
Qichujine=x.Sum(y=>y.qichujine)
}
var query = from v in db.VW_CUIHUN
where (v.Pinhao == "shuxue" && v.Yearmonth < DateTime.Parse("2010-09-16") && v.Yearmonth > DateTime.Parse("2010-09-13") && v.Qichujine > 0)
group v by ( v.Pinhao ) into newVw_cuihun
select new
{
fenlei = newVw_cuihun.First().Fenlei,
pinhao = newVw_cuihun.First().Pinhao,
jine = newVw_cuihun.Sum(v => v.Jine),
qichujine = newVw_cuihun.Sum(v => v.Qichujine)
};
备注:Linq Group by 不支持多条件
不是吧。。自己google一下
都会报错
无法按类型“<>f__AnonymousType0`2[System.Type]”排序。
fenlei = newVw_cuihun.First().Fenlei,
pinhao = newVw_cuihun.First().Pinhao,有错误,我换成
fenlei=x.Select(v => v.fenlei),
pinhao=x.Select(v => v.pinhao),能通过调试但是内容全部都为空了?这会是什么原因呢?真奇怪啊
pinhao = newVw_cuihun.First().Pinhao,
fenlei和pinhao列也加进group by 里面,然后在select中:
select new
{
fenlei = newVw_cuihun.key.pinhao,
pinhao = newVw_cuihunkey.fenlei,
jine = newVw_cuihun.Sum(v => v.Jine),
qichujine = newVw_cuihun.Sum(v => v.Qichujine)
};谢谢几位啊~