SQL

《按每天，某个时间段》

select A,B,C,DATETIME,CAST(CONVERT(varchar(100), DATETIME, 112) AS INT ) AS daytime

from  TABLE1

WHERE 1=1

AND CAST(DATETIME AS TIME) BETWEEN '21:00:00' AND '23:59:59'

AND DATETIME>='2016-07-11' and DATETIME<'2016-07-15'

group by A,B,C,DATETIME

---------------------------------------------

《WITH AS 递归获取部门梯队》

WITH dtTree

AS (

SELECT * FROM table1 ur

WHERE DepartId = 0

UNION ALL    --第二个查询作为递归成员， 下属成员的结果为空时，此递归结束。

SELECT urTree.*    FROM dtTree

INNER JOIN  table1  urTree

ON dtTree.DepartId = urTree.DepartId  and urTree.NAME  =dtTree.NAME  

)

 

---------------------------------------------

《LINQ 合并集合后分组某一个字段》

List<AppCallStatisticsModel> data3 = new List<AppCallStatisticsModel>();
List<AppCallStatisticsModel> data2 = AppCallStatisticService.GetConnectRatePcInfo(searchModel);
List<AppCallStatisticsModel> data1 = AppCallStatisticService.GetConnectRateAppInfo(searchModel);
data3.AddRange(data2);
data3.AddRange(data1);

var query = from t in data3
group t by t.SearchDate into g
select new AppCallStatisticsModel()
{
SearchDate = g.Key,
SeatCallPercent =g.Sum(c=>ConvertHelper.ToDecimal(c.SeatCallPercent)).ToString("0.00"),
APPCallPercent = g.Sum(c=>ConvertHelper.ToDecimal(c.APPCallPercent)).ToString("0.00")
};