LINQ的SQL命令(透视) [英] SQL command to LINQ (pivoting)
本文介绍了LINQ的SQL命令(透视)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在将数据库从MySQL(使用的ODBC)转移到MS SQL,我想将SQL查询翻译"到LINQ.有人可以帮我吗(每个位置的SUM收费列和按月分组的结果):
I'm moving DB from MySQL (used ODBC) to MS SQL and I want to "translate" SQL queries to LINQ. Can someone help me with this (it should SUM Charge column for every location and group result by months):
SELECT
sum(case when Location="Location1" then Charge else 0 end) as Location1,
sum(case when Location="Location2" then Charge else 0 end) as Location2,
sum(case when Location="Location3" then Charge else 0 end) as Location3,
MAKEDATE(YEAR(OrderTime),DAYOFYEAR(OrderTime)) AS date FROM Sales
GROUP BY YEAR(OrderTime),MONTH(OrderTime)
ORDER BY OrderTime DESC
?
输出应如下所示:
Location1 | Location2 | Location3 | date
我尝试从这里使用LINQ示例:
I tryed to use LINQ sample from here:
var query = context.log_sales
.GroupBy(c => c.OrderTime)
.Select(g => new
{
Date = g.Key,
Location1 = g.Where(c => c.Location == "Location1").Sum(c => c.Charge) ?? 0,
Location2 = g.Where(c => c.Location == "Location2").Sum(c => c.Charge) ?? 0
}).ToList();
这几乎是我所需要的.也应该按年份分组,我不知道该怎么做.
and it is almost what I need. There should be grouping by year too and I don't know how to do this.
推荐答案
这可能有帮助.
context.log_sales
.GroupBy(s => new {Year = OrderTime.Year, Month = OrderTime.Month})
.Select
( g => new {
Date = new DateTime(g.Key.Year, g.Key.Month, 1),
Location1 = g.Where(s => s.Location == "Location1").Sum(s => s.Charge),
Location2 = g.Where(s => s.Location == "Location2").Sum(s => s.Charge),
Location3 = g.Where(s => s.Location == "Location3").Sum(s => s.Charge),
}
)
.OrderBy(x => x.Date);
这篇关于LINQ的SQL命令(透视)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文