8,497
社区成员
发帖
与我相关
我的任务
分享
-- Region Parameters
DECLARE @p0 DateTime = '2012-03-10 00:00:00.000'
DECLARE @p1 DateTime = '2012-03-15 00:00:00.000'
DECLARE @p2 Int = 1
DECLARE @p3 Int = 0
DECLARE @p4 Int = 1
-- EndRegion
SELECT [t2].[value] AS [Num], [t2].[value2] AS [SUMA], [t2].[value3] AS [SUMB], [t2].[value4] AS [SUMC]
FROM (
SELECT COUNT(*) AS [value], SUM([t1].[a]) AS [value2], SUM([t1].[b]) AS [value3], SUM([t1].[c]) AS [value4], [t1].[value] AS [value5]
FROM (
SELECT
(CASE
WHEN ([t0].[time] > @p0) AND ([t0].[time] < @p1) THEN @p2
ELSE @p3
END) AS [value], [t0].[a], [t0].[b], [t0].[c]
FROM [TA] AS [t0]
) AS [t1]
GROUP BY [t1].[value]
) AS [t2]
WHERE [t2].[value5] = @p4
Num SUMA SUMB SUMC
----------- ----------- ----------- -----------
2 10 15 5
(1 row(s) affected)
//修改一下实现思路:
from a in TAs
group a by new{time=(a.Time>Convert.ToDateTime("2012-03-10") && a.Time<Convert.ToDateTime("2012-03- 15"))?1:0}
into g
where g.Key.time==1
select new
{
Num=g.Count(),
SUMA=g.Sum(x=>x.A),
SUMB=g.Sum(x=>x.B),
SUMC=g.Sum(x=>x.C),
}
CREATE TABLE TA(id int,a int,b int,c int,time DateTime)
INSERT TA SELECT 1,5,5,5,'2012-02-12' UNION ALL
SELECT 2,10,5,0,'2012-03-12' UNION ALL
SELECT 3,0,10,5,'2012-03-13'
select COUNT(id) as Num,
SUM(a) as SumA,
SUM(b) as SumB,
SUM(c) as SumC
from TA
where time>'2012-03-10' and time<'2012-03-15'
Num SumA SumB SumC
----------- ----------- ----------- -----------
2 10 15 5
(1 row(s) affected)
(from b in TAs
let g=TAs.Where(a=>a.Time>Convert.ToDateTime("2012-03-10")
&& a.Time<Convert.ToDateTime("2012-03- 15") )
select new
{
Num=g.Count(),
SUMA=g.Sum(x=>x.A),
SUMB=g.Sum(x=>x.B),
SUMC=g.Sum(x=>x.C),
}).Distinct()