Full-house

从昨天下午三点开始做这一个查询，到今天下午两点钟，终于搞定了。

BEGIN
Set @tsql=’Select C.CheckerID,C.CheckerName,isnull(D.date1,D.date2) date1,D.CSum1,D.QTY1,D.CSum2,D.QTY2
from (Select CheckerID, CheckerName from CheckerList where CheckerType=1) C
full join
(Select A.CSum1,A.QTY1,B.CSum2,B.QTY2,A.date1,B.date2,A.Checker Checker1,B.Checker Checker2 From
(Select count(CarQTY) as CSum1,sum(cast(CarQTY as bigint))  as QTY1,
CONVERT(varchar(100), FormDate, 23) date1,Checker
from FeedBack
where FormDate between ”’+@StartDate+”’ and ”’+@EndDate+”’
and (FeedBackType=1 or FeedBackType=2)
group by  CONVERT(varchar(100), FormDate, 23),Checker) A
full join
(Select count(CarQTY) as CSum2,sum(cast(CarQTY as bigint))  as QTY2,
CONVERT(varchar(100), FormDate, 23) date2,Checker
from FeedBack
where FormDate between ”’+@StartDate+”’ and ”’+@EndDate+”’
and (FeedBackType=3 or FeedBackType=4)
group by  CONVERT(varchar(100), FormDate, 23),Checker) B
On ( A.Checker=B.Checker and A.date1=B.date2)
) D
On (C.CheckerName=D.Checker1 or C.CheckerName=D.Checker2)
order by C.CheckerID’
END

下面附上两个图：（查询分析图，程序最终UI图）

这个里面用了两个比较重要的full join, 以前一直觉得full join 用的情况不多，这一次总算帮我我的忙了。
情况是这样的，t1 表中有数据，t2表中也有数据，两个表中的某些字段相同时需要将两个表中的值合并，不相同时也需要列出来，但是需要将数据以每一个操作工，每天的情况列出来，如果操作工没有数据，也需在将操作工的名字列出来。
OK， 人才。

left join   :左连接，返回左表中所有的记录以及右表中连接字段相等的记录（右表中不存在的显示NULL）。
right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录（左表中不存在的显示NULL）。
inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。
full join:外连接，返回两个表中的行：left join + right join (两个表中都可能出现NULL)
cross join:结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。
补充说明：如果表一中的某行对应表二中的两行记录，那么结果集中会有两条记录对应，right join也同样道理。
——————————————————————-
例子

declare @a table(a int,b int)
declare @b table(a int,b int)

insert @a values(1,1)
insert @a values(1,1)
insert @a values(2,2)
insert @b values(1,1)
insert @b values(3,3)
select * from @a
select * from @b
--左:
select * from @a Aa left join @b Bb on Aa.a=Bb.a
--右：
select * from @a Aa right join @b Bb on Aa.a=Bb.a
--内
select * from @a Aa inner join @b Bb on Aa.a=Bb.a
--外：
select * from @a Aa full join @b Bb on Aa.a=Bb.a
--交叉连接
select * from @a cross join @b

MSSQL2005时间字段格式化
1 取值后格式化
{0:d}小型：如2005-5-6
{0:D}大型：如2005年5月6日
{0:f}完整型
2 当前时间获取
DateTime.Now.ToShortDateString

3 取值中格式化
SQL Server里面可能经常会用到的日期格式转换方法:
sql server使用convert来取得datetime日期数据，以下实例包含各种日期格式的转换
语句及查询结果：
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

C#中Datetime的常用格式

用法大致如下，

DateTime curDate;

curDate.ToString(” D” ); 输出时间格式 2008年5月3日
curDate.ToString(” d” ); 输出时间格式 2008-5-3
curDate.ToString(“F” ); 输出时间格式 2008年5月3日 3:14:52
curDate.ToString(“f” ); 输出时间格式 2008年5月3日 3:14
curDate.ToString(“G” ); 输出时间格式 2008-5-3 3:14:52
curDate.ToString(“g” ); 输出时间格式 2008-5-3 3:14
curDate.ToString(“M” ); 输出时间格式 5月3日
curDate.ToString(“m” ); 输出时间格式 5月3日
curDate.ToString(“O” ); 输出时间格式 2008-05-03T03:14:52.8125000-05:00
curDate.ToString(“o” ); 输出时间格式 2008-05-03T03:14:52.8125000-05:00
curDate.ToString(“R” ); 输出时间格式 Sat, 03 May 2008 03:14:52 GMT
curDate.ToString(“r” ); 输出时间格式 Sat, 03 May 2008 03:14:52 GMT
curDate.ToString(“s” ); 输出时间格式 2008-05-03T03:14:52
curDate.ToString(“T” ); 输出时间格式 3:14:52
curDate.ToString(“t” ); 输出时间格式 3:14
curDate.ToString(“U” ); 输出时间格式 2008年5月3日 8:14:52
curDate.ToString(“u” ); 输出时间格式 2008-05-03 03:14:52Z
curDate.ToString(“Y” ); 输出时间格式 2008年5月
curDate.ToString(“y” ); 输出时间格式 2008年5月

4. DateTime 自定义格式

就是用一些参数组合起来的意思。相关参数如下

d 就是日期。 如1，2，20等。

dd就是二位数日期，如01, 02, 22等

ddd 就是三位字母的星期。如： Mon，Tue等

dddd就是星期的全名，如Monday, Sunday 等。

f 就是秒的小数部分。 如 SS.f，就是几秒几的意思。

ff就是秒的两位小数部分。fff, fffff,以及FF，FFF意思都差不多，就是几位小数的意思。

g就是在时间后面的额外部分，如B.C， A.D等等

h，0-12的小时。hh，两位表述的小时。

H，0-23的小时。HH，两位表述的小时。

K，表示当前的时区号。

m,mm,表示不限位和两位表示的分钟。

M，MMM，MMMM表示月份。分别如： 1， Jan， January

s, ss 表示不限位和两位的秒。如1， 01等

t, tt,表示上午下午， AM, 或者PM

y, yy, yyyy,yyyyy不同位数的年份

z,zz,zzzz不同位数的时区

操作时间函数————————-
1、比较时间大小的实验
string st1=”12:13″;
string st2=”14:14″;
DateTime dt1=Convert.ToDateTime(st1);
DateTime dt2=Convert.ToDateTime(st2);
DateTime dt3=DateTime.Now;
if(DateTime.Compare(dt1,dt2)>0)
msg.Text=st1+”>”+st2;
else
msg.Text=st1+”<"+st2;
msg.Text+="\r\n"+dt1.ToString();
if(DateTime.Compare(dt1,dt3)>0)
msg.Text+=”\r\n”+st1+”>”+dt3.ToString();
else
msg.Text+=”\r\n”+st1+”<"+dt3.ToString();

2、计算两个时间差值的函数，返回时间差的绝对值：
private string DateDiff(DateTime DateTime1,DateTime DateTime2)
{
string dateDiff=null;
try
{
TimeSpan ts1=new TimeSpan(DateTime1.Ticks);
TimeSpan ts2=new TimeSpan(DateTime2.Ticks);
TimeSpan ts=ts1.Subtract(ts2).Duration();
dateDiff=ts.Days.ToString()+"天"
+ts.Hours.ToString()+"小时"
+ts.Minutes.ToString()+"分钟"
+ts.Seconds.ToString()+"秒";
}
catch
{

}
return dateDiff;
}

3、实现计算DateTime1－36天＝DateTime2的功能

TimeSpan ts=new TimeSpan(40,0,0,0);
DateTime dt2=DateTime.Now.Subtract(ts);
msg.Text=DateTime.Now.ToString()+"-"+ts.Days.ToString()+"天\r\n";
msg.Text+=dt2.ToString();

原文地址：http://worm128.blog.163.com/blog/static/5677214920081125814465/

今天在修改一个项目的时候要实现一功能：将系统中图表中的数据每天存档起来，以备查看。
一开始我用想用表变量来实现，先将数据整理到表变量中，然后插入到相应的存档数据库中。

Declare @chart table(room int,qty1 int,qty3 int)
//-------------------
insert into @chart
Select a.room,isnull(b.Qty1,0) qty1,isnull(d.Qty3,0) qty3
from t_room_all a
left join ( Select Room, sum(convert(int,OrderQty))-Sum(PrintedQty) Qty1
from t_pop_all where Status=0 or Status=1 group by Room) b on a.room = b.room
left join ( Select Room, sum(convert(int,OrderQty))-Sum(PrintedQty) Qty2
from t_pop_all where Status=1 group by Room) c
on a.room = c.room left join ( Select Room, sum(convert(int,ProducedQty))-Sum(PrintedQty) Qty3
from t_pop_all where Status=0 or Status=1 group by Room ) d
on a.room = d.room
//--------------------
Insert into t_chart_history (room,qty1,qty3) select * from @chart

不过后来我再整理了一下，直接用一句SQL就可以了，不用表变量了。

Insert into t_chart_history (room,qty1,qty3)
Select a.room,isnull(b.Qty1,0) qty1,isnull(d.Qty3,0) qty3
from t_room_all a
left join ( Select Room, sum(convert(int,OrderQty))-Sum(PrintedQty) Qty1
from t_pop_all where Status=0 or Status=1 group by Room) b on a.room = b.room
left join ( Select Room, sum(convert(int,OrderQty))-Sum(PrintedQty) Qty2
from t_pop_all where Status=1 group by Room) c
on a.room = c.room left join ( Select Room, sum(convert(int,ProducedQty))-Sum(PrintedQty) Qty3
from t_pop_all where Status=0 or Status=1 group by Room ) d
on a.room = d.room

这个SQL语句比较复杂，一般采用分段写，最后整合成一个长句。