|
一、年月周統(tǒng)計(jì)DEMO
公司正式數(shù)據(jù)庫(kù)比較復(fù)雜，加上表內(nèi)數(shù)據(jù)不便輕易修改，所以在項(xiàng)目之前，我新建了個(gè)數(shù)據(jù)表用來(lái)測(cè)試代碼的可行性，正式項(xiàng)目是體力活，但知識(shí)點(diǎn)
就這些。
圖是一個(gè)存放康城小區(qū)、紅河小區(qū)2012年1月份的水電費(fèi)表(項(xiàng)目中，這是一多張表組成的臨時(shí)表，字段要多一些，但表結(jié)構(gòu)一致)。
項(xiàng)目的要求是：按照年、月、周、日分別對(duì)其進(jìn)行統(tǒng)計(jì)，按日統(tǒng)計(jì)比較簡(jiǎn)單，不再累述。
為了方便測(cè)試，我在表內(nèi)添加兩條新數(shù)據(jù)。
1 insert into EnergySettings(PointID,ValueTime,BuildName,FieldName,Value) values('A','2011-12-31','康城小區(qū)','電費(fèi)','100')
2
3 insert into EnergySettings(PointID,ValueTime,BuildName,FieldName,Value) values('A','2012-2-1','紅河小區(qū)','水費(fèi)','100') 此時(shí)表內(nèi)，含有2011年12月一條數(shù)據(jù)，2012年1月31*4條數(shù)據(jù)，和2012年2月一條數(shù)據(jù)。
1按年統(tǒng)計(jì)(用到YEAR()函數(shù))
1 select PointID,YEAR(ValueTime)as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue from EnergySettings
2 group by PointID,YEAR(ValueTime),BuildName,FieldName 
2按月統(tǒng)計(jì)(用到MONTH()函數(shù))
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按周統(tǒng)計(jì)(用到DATEPART()函數(shù))
按周統(tǒng)計(jì)需要注意兩個(gè)細(xì)節(jié)：
?、?中西方觀念不同，西方把周日作為一周的開(kāi)始，我們習(xí)慣把周一作為一周的第一天。在SQL Server中，默認(rèn)周日為一周的開(kāi)始，所以我們需要根
據(jù)業(yè)務(wù)需求(set datefirst )來(lái)對(duì)一周的第一天是星期一還是星期日進(jìn)行設(shè)置。
② 本年的第一個(gè)周和去年的最后一周是分開(kāi)的，比如， 2011-12-31是星期六，2012-01-01是星期日，如果我們?cè)O(shè)置周一為一周的第一天，那
么2011-12-31是2011年第53周，2012-01-01是2012年第一周，這兩周分開(kāi)，也就是說(shuō)2011年第53周有6天，2012年第一周只有1天。并不能籠
統(tǒng)地說(shuō)2011年第53周就是2012年第一周。
以康城小區(qū)的電費(fèi)為例：
默認(rèn)下，周日為周起始：
1 select PointID,YEAR(ValueTime)*100+DATEPART(week,ValueTime) as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue
2 from EnergySettings where BuildName='康城小區(qū)'and FieldName='電費(fèi)'
3 group by PointID,YEAR(ValueTime)*100+DATEPART(week,ValueTime),BuildName,FieldName 
我們可以設(shè)置周一為一周開(kāi)始：
set datefirst 1 --設(shè)置周一為一周的開(kāi)始/datefirst 7=周日為一周開(kāi)始
select PointID,YEAR(ValueTime)*100+DATEPART(week,ValueTime) as NewValueTime,BuildName,FieldName,SUM(Value)as NewValue
from EnergySettings where BuildName='康城小區(qū)'and FieldName='電費(fèi)'group by PointID,YEAR(ValueTime)*100+DATEPART(w
eek,ValueTime),BuildName,FieldName
項(xiàng)目界面展示的時(shí)候，我們可以用下面GetNewWeekDays()方法把 201202轉(zhuǎn)化為20120102～20120108這種格式(此方法在單獨(dú)一年可用，
連續(xù)多年時(shí)需稍作修改，原因就是上面我們提到的細(xì)節(jié)②)。
1 /// <summary>
2 /// 根據(jù)傳值得周一到周日日期
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)//如果第一天是周日，本年第一個(gè)周日=今天
8 yearFirstSunday = yearFirstDay;
9 else
10 yearFirstSunday = yearFirstDay.AddDays(7 - (int)yearFirstDay.DayOfWeek);
11 NewWeekSunday = yearFirstSunday.AddDays((Convert.ToInt32(yearWeekindex.Substring(4)) - 1) * 7);//得第個(gè)星期的周日
12 return NewWeekSunday.AddDays(-6).ToString("yyyyMMdd") + "～?" + NewWeekSunday.ToString("yyyyMMdd");
13 }
二、SQL時(shí)間函數(shù)總結(jié)：
1、SQL Server中主要的時(shí)間函數(shù)有：
①getdate()：返回當(dāng)前系統(tǒng)日期、時(shí)間
select 當(dāng)前系統(tǒng)時(shí)間=GETDATE() 
②dateadd(interval,number,date)：返回指定日期加一段時(shí)間后的新datetime。
select 倒退一天=DATEADD(day,-1,getdate()), 前進(jìn)一小時(shí)=DATEADD(hour,1,getdate()) 
③datediff(interval,date1,date2): 返回跨兩個(gè)指定時(shí)間的差值。
1 DATEDIFF(DAY,'2011-12-5','2012-1-1') --返回：27
2
3 select 天數(shù)=datediff(DAY,'2012-01-01','2013-01-01'), 月數(shù)=datediff(MONTH,'2013-01-01','2012-01-01')
?、躣atepart(interval,date)和datename(interval,date):都是返回指定日期interval部分的值，不同的是，datepart()返回int類(lèi)型的整數(shù)，
datename返回nvarchar類(lèi)型的字符串。
1 DATEPART(YEAR,'2011-12-5') -–datepart()返回：2011 (int類(lèi)型)
2 DATENAME (YEAR,'2011-12-5') -–datename()返回：2011 (nvarchar類(lèi)型)
3
4 DATEPART(MONTH,'2011-12-5') -–datepart()返回：12 (int類(lèi)型)
5 DATENAME(MONTH,'2011-12-5') –-datename()返回：December(nvarchar類(lèi)型)
在此，把不同參數(shù)interval羅列出來(lái)，我們可以更清晰地看出，datepart()和datename()的區(qū)別。
1 select '當(dāng)前時(shí)間(datepart)'=GETDATE(), 年=DATEPART(YEAR,GETDATE()),季=DATEPART(QUARTER,GETDATE()),月=DATEPART(Month,GETDATE()),
2 周=DATEPART(WEEK,GETDATE()),周日數(shù)=DATEPART(WEEKDAY,GETDATE()),日=DATEPART(DAY,GETDATE()),
3 年日數(shù)=DATEPART(DAYOFYEAR,GETDATE()),時(shí)=DATEPART(HOUR,GETDATE()),分=DATEPART(MINUTE,GETDATE()),
4 秒=DATEPART(SECOND,GETDATE()),毫秒=DATEPART(MILLISECOND,GETDATE())
5
6 select '當(dāng)前時(shí)間(datename)'=GETDATE(), 年=DATENAME(YEAR,GETDATE()),季=DATENAME(QUARTER,GETDATE()),月=DATENAME(Month,GETDATE()),
7 周=DATENAME(WEEK,GETDATE()),周日數(shù)=DATENAME(WEEKDAY,GETDATE()),日=DATENAME(DAY,GETDATE()),
8 年日數(shù)=DATENAME(DAYOFYEAR,GETDATE()),時(shí)=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、時(shí)間函數(shù)的interval參數(shù)及其縮寫(xiě)
|
縮 寫(xiě)（Sql Server） |
Access 和 ASP |
說(shuō)明 |
|
Year |
Yy |
yyyy |
年 (1753 ~ 9999) |
|
Quarter |
Qq |
q |
季 (1 ~ 4) |
|
Month |
Mm |
m |
月 (1 ~ 12) |
|
Week |
Wk |
ww |
周(一年中的第幾周 0 ~ 51) |
|
Weekday |
Dw |
w |
一周的日數(shù)，一周中第幾日 1-7 |
|
Day |
Dd |
d |
日，1-31 |
|
Day of year |
Dy |
y |
一年的日數(shù),一年中第幾日 1-366 |
|
Hour |
Hh |
h |
時(shí)0 ~ 23 |
|
Minute |
Mi |
n |
分鐘0 ~ 59 |
|
Second |
Ss |
s |
秒 0 ~ 59 |
|
Millisecond |
Ms |
- |
毫秒 0 ~ 999 |
3、日期格式轉(zhuǎn)化
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#中時(shí)間字符串的格式轉(zhuǎn)化
日期格式：yyyyMMdd HH:mm:ss (注意大小寫(xiě))
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小時(shí)
18 dt.AddMinutes(1).ToString(); //增加1分鐘
19 dt.AddSeconds(1).ToString(); //增加1秒
20 dt.AddMilliseconds(1).ToString(); //增加1毫秒 //去重復(fù)
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)
|
