|
一、年月周统计DEMO
公司正式数据库比较复杂，加上表内数据不便轻易修改，所以在项目之前，我新建了个数据表用来测试代码的可行性，正式项目是体力活，但知识点
就这些。
图是一个存放康城小区、红河小区2012年1月份的水电费表(项目中，这是一多张表组成的临时表，字段要多一些，但表结构一致)。
项目的要求是：按照年、月、周、日分别对其进行统计，按日统计比较简单，不再累述。
为了方便测试，我在表内添加两条新数据。
1 insert into EnergySettings(PointID,ValueTime,BuildName,FieldName,Value) values('A','2011-12-31','康城小区','电费','100')
2
3 insert into EnergySettings(PointID,ValueTime,BuildName,FieldName,Value) values('A','2012-2-1','红河小区','水费','100') 此时表内，含有2011年12月一条数据，2012年1月31*4条数据，和2012年2月一条数据。
1按年统计(用到YEAR()函数)
1 select PointID,YEAR(ValueTime)as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue from EnergySettings
2 group by PointID,YEAR(ValueTime),BuildName,FieldName 
2按月统计(用到MONTH()函数)
1 select PointID,YEAR(ValueTime)*100+MONTH(ValueTime)as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue
2 from EnergySettings
3 group by PointID,YEAR(ValueTime)*100+MONTH(ValueTime),BuildName,FieldName 
3按周统计(用到DATEPART()函数)
按周统计需要注意两个细节：
① 中西方观念不同，西方把周日作为一周的开始，我们习惯把周一作为一周的第一天。在SQL Server中，默认周日为一周的开始，所以我们需要根
据业务需求(set datefirst )来对一周的第一天是星期一还是星期日进行设置。
② 本年的第一个周和去年的最后一周是分开的，比如， 2011-12-31是星期六，2012-01-01是星期日，如果我们设置周一为一周的第一天，那
么2011-12-31是2011年第53周，2012-01-01是2012年第一周，这两周分开，也就是说2011年第53周有6天，2012年第一周只有1天。并不能笼
统地说2011年第53周就是2012年第一周。
以康城小区的电费为例：
默认下，周日为周起始：
1 select PointID,YEAR(ValueTime)*100+DATEPART(week,ValueTime) as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue
2 from EnergySettings where BuildName='康城小区'and FieldName='电费'
3 group by PointID,YEAR(ValueTime)*100+DATEPART(week,ValueTime),BuildName,FieldName 
我们可以设置周一为一周开始：
set datefirst 1 --设置周一为一周的开始/datefirst 7=周日为一周开始
select PointID,YEAR(ValueTime)*100+DATEPART(week,ValueTime) as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue
from EnergySettings where BuildName='康城小区'and FieldName='电费'group by PointID,YEAR(ValueTime)*100+DATEPART(w
eek,ValueTime),BuildName,FieldName
项目界面展示的时候，我们可以用下面GetNewWeekDays()方法把 201202转化为20120102～20120108这种格式(此方法在单独一年可用，
连续多年时需稍作修改，原因就是上面我们提到的细节②)。
1 /// <summary>
2 /// 根据传值得周一到周日日期
3 /// </summary>
4 private string GetNewWeekDays(string yearWeekindex)
5 {
6 DateTime yearFirstDay = Convert.ToDateTime(yearWeekindex.Substring(0, 4) + "-01-01");//获得本年第一天的日期
7 if (yearFirstDay.DayOfWeek == 0)//如果第一天是周日，本年第一个周日=今天
8 yearFirstSunday = yearFirstDay;
9 else
10 yearFirstSunday = yearFirstDay.AddDays(7 - (int)yearFirstDay.DayOfWeek);
11 NewWeekSunday = yearFirstSunday.AddDays((Convert.ToInt32(yearWeekindex.Substring(4)) - 1) * 7);//得第个星期的周日
12 return NewWeekSunday.AddDays(-6).ToString("yyyyMMdd") + "～?" + NewWeekSunday.ToString("yyyyMMdd");
13 }
二、SQL时间函数总结：
1、SQL Server中主要的时间函数有：
①getdate()：返回当前系统日期、时间
②dateadd(interval,number,date)：返回指定日期加一段时间后的新datetime。
select 倒退一天=DATEADD(day,-1,getdate()), 前进一小时=DATEADD(hour,1,getdate()) 
③datediff(interval,date1,date2): 返回跨两个指定时间的差值。
1 DATEDIFF(DAY,'2011-12-5','2012-1-1') --返回：27
2
3 select 天数=datediff(DAY,'2012-01-01','2013-01-01'), 月数=datediff(MONTH,'2013-01-01','2012-01-01')
④datepart(interval,date)和datename(interval,date):都是返回指定日期interval部分的值，不同的是，datepart()返回int类型的整数，
datename返回nvarchar类型的字符串。
1 DATEPART(YEAR,'2011-12-5') -–datepart()返回：2011 (int类型)
2 DATENAME (YEAR,'2011-12-5') -–datename()返回：2011 (nvarchar类型)
3
4 DATEPART(MONTH,'2011-12-5') -–datepart()返回：12 (int类型)
5 DATENAME(MONTH,'2011-12-5') –-datename()返回：December(nvarchar类型)
在此，把不同参数interval罗列出来，我们可以更清晰地看出，datepart()和datename()的区别。
1 select '当前时间(datepart)'=GETDATE(), 年=DATEPART(YEAR,GETDATE()),季=DATEPART(QUARTER,GETDATE()),月=DATEPART(Month,GETDATE()),
2 周=DATEPART(WEEK,GETDATE()),周日数=DATEPART(WEEKDAY,GETDATE()),日=DATEPART(DAY,GETDATE()),
3 年日数=DATEPART(DAYOFYEAR,GETDATE()),时=DATEPART(HOUR,GETDATE()),分=DATEPART(MINUTE,GETDATE()),
4 秒=DATEPART(SECOND,GETDATE()),毫秒=DATEPART(MILLISECOND,GETDATE())
5
6 select '当前时间(datename)'=GETDATE(), 年=DATENAME(YEAR,GETDATE()),季=DATENAME(QUARTER,GETDATE()),月=DATENAME(Month,GETDATE()),
7 周=DATENAME(WEEK,GETDATE()),周日数=DATENAME(WEEKDAY,GETDATE()),日=DATENAME(DAY,GETDATE()),
8 年日数=DATENAME(DAYOFYEAR,GETDATE()),时=DATENAME(HOUR,GETDATE()),分=DATENAME(MINUTE,GETDATE()),
9 秒=DATENAME(SECOND,GETDATE()),毫秒=DATENAME(MILLISECOND,GETDATE())
⑤year(), month(),day()：返回指定日期的年、月、日。
1 YEAR('2011-12-5')=DATEPART(YEAR,'2011-12-5') --返回：2011
2 MONTH('2011-12-5')= DATEPART(Month,'2011-12-5') --返回：12
3 DAY('2011-12-5')= DATEPART(DAY,'2011-12-5') --返回：5
2、时间函数的interval参数及其缩写
|
缩 写（Sql Server） |
Access 和 ASP |
说明 |
|
Year |
Yy |
yyyy |
年 (1753 ~ 9999) |
|
Quarter |
Qq |
q |
季 (1 ~ 4) |
|
Month |
Mm |
m |
月 (1 ~ 12) |
|
Week |
Wk |
ww |
周(一年中的第几周 0 ~ 51) |
|
Weekday |
Dw |
w |
一周的日数，一周中第几日 1-7 |
|
Day |
Dd |
d |
日，1-31 |
|
Day of year |
Dy |
y |
一年的日数,一年中第几日 1-366 |
|
Hour |
Hh |
h |
时0 ~ 23 |
|
Minute |
Mi |
n |
分钟0 ~ 59 |
|
Second |
Ss |
s |
秒 0 ~ 59 |
|
Millisecond |
Ms |
- |
毫秒 0 ~ 999 |
3、日期格式转化
1 select CONVERT(varchar, getdate(), 120 ) --2011-12-05 15:54:48
2 select CONVERT(varchar(12) , getdate(), 111 ) --2011/12/05
3 select CONVERT(varchar(12) , getdate(), 102 ) --2011.12.05
4 select CONVERT(varchar(12) , getdate(), 112 ) --20111205
5 select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','') --20111205155448
三、C#中时间字符串的格式转化
日期格式：yyyyMMdd HH:mm:ss (注意大小写)
1 DateTime dt = DateTime.Now;
2 timestr = dt.ToString(); //2011-12-05 16:32:26
3 timestr = dt.ToShortDateString(); //2005-11-5
4 timestr = dt.ToShortTimeString(); //16:32
5 timestr = dt.ToLongDateString(); //2011-12-05
6 timestr = dt.ToLongTimeString(); //16:32:26
7 timestr = string.Format("{0:yyyy-HH-dd HH:mm:ss}", dt);//2011-12-05 16:32:26
8 timestr = string.Format("{0:yyyy/HH/dd}", dt); //2011/12/05
9 timestr = string.Format("{0:f}", dt); //2011年12月5日 16:32
10 timestr = string.Format("{0:F}", dt); //2011年12月5日 16:32:26
11 timestr = string.Format("{0:g}", dt); //2011/12/5 16:32
12 timestr = string.Format("{0:G}", dt); //2011/12/5 16:32:26
13
14 dt.AddYears(1).ToString(); //增加1年
15 dt.AddMonths(1).ToString(); //增加1月
16 dt.AddDays(1).ToString(); //增加1天
17 dt.AddHours(1).ToString(); //增加1小时
18 dt.AddMinutes(1).ToString(); //增加1分钟
19 dt.AddSeconds(1).ToString(); //增加1秒
20 dt.AddMilliseconds(1).ToString(); //增加1毫秒 //去重复
delete from friend where id in (select id from
(select id,userid, row_number() over(partition by userid order by id) as rn from friend ) as a where rn > 1)
|
