日期:2014-05-20  浏览次数:20973 次

求解SQL翻译成LINQ语句 高手进


select top 5 c.name,c.userid,count(d.userid) from users_base c join game_user d on c.userid=d.userid group by c.name,c.userid


统计每个人玩游戏个数

O(∩_∩)O




------解决方案--------------------
参考
SQL code

from c in Customers
join p in Purchases
on c.ID equals p.CustomerID
group p.Price by p.Date.Year into salesByYear
select new
{
Year=salesByYear.Key,
TotalValue =salesByYear.Sum()
}
 

     最后呈现两个内容,一个是年份,一个是所有客户消费的金额总和。


     对应的SQL:

SELECT SUM([t2].[Price]) AS [TotalValue], [t2].[value] AS [Year]
FROM (
    SELECT DATEPART(Year, [t1].[Date]) AS [value], [t1].[Price]
    FROM [Customer] AS [t0]
    INNER JOIN [Purchase] AS [t1] ON ([t0].[ID]) = [t1].[CustomerID]
    ) AS [t2]
GROUP BY [t2].[value]

------解决方案--------------------
SQL code

(from c1 in c
join d1 in d
on c.userid equals d.userid
group d1.userid by 
{
    c.name,
    c.userid 
}
into grouptable
select new 
{
    names=grouptable.Key.name,
    userid=grouptable.Key.userid,
    countuserid=grouptable.Sum()
    
}).Take(5)