这样的结果用SQL语句如何写? ( 积分: 100 )

  • 主题发起人 主题发起人 kukow
  • 开始时间 开始时间
sql1&nbsp;:=&nbsp;'select&nbsp;a.Field1,a.Field2,a.Field3,substring(a.Field4,0,11)&nbsp;f,'+<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'substring(a.Field4,11,20)&nbsp;ff'&nbsp;;<br>&nbsp;&nbsp;sql2&nbsp;:=&nbsp;'from&nbsp;表名&nbsp;a&nbsp;'&nbsp;;<br>&nbsp;&nbsp;sql3&nbsp;:=&nbsp;'where&nbsp;a.field1&nbsp;=001&nbsp;and&nbsp;a.field2&nbsp;=张三&nbsp;and&nbsp;a.field3&nbsp;=00000001&nbsp;and&nbsp;&nbsp;&nbsp;substring(a.Field4,0,10)&nbsp;='+'1000-00-00';<br>&nbsp;&nbsp;for&nbsp;i&nbsp;:=&nbsp;2&nbsp;to&nbsp;n&nbsp;do<br>&nbsp;&nbsp;begin<br>&nbsp;&nbsp;&nbsp;&nbsp;s&nbsp;&nbsp;&nbsp;&nbsp;:=&nbsp;chr(i+64)+'.field4'&nbsp;&nbsp;;<br>&nbsp;&nbsp;&nbsp;&nbsp;sql1&nbsp;:=&nbsp;sql1&nbsp;+&nbsp;',substring('+s+',11,20)'&nbsp;&nbsp;&nbsp;&nbsp;;<br>&nbsp;&nbsp;&nbsp;&nbsp;sql2&nbsp;:=&nbsp;sql2&nbsp;+&nbsp;',表名&nbsp;'+chr(i+64)&nbsp;&nbsp;&nbsp;&nbsp;;<br>&nbsp;&nbsp;&nbsp;&nbsp;sql3&nbsp;:=&nbsp;sql3&nbsp;+&nbsp;'&nbsp;and&nbsp;a.field1&nbsp;='+chr(i+64)+'.field1&nbsp;and&nbsp;a.field2&nbsp;='+chr(i+64)+'.field2&nbsp;and'+<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'&nbsp;a.field3&nbsp;='+chr(i+64)+'.field3'+&nbsp;'&nbsp;and&nbsp;substring(a.Field4,0,11)&nbsp;=&nbsp;substring('+chr(i+64)+'.Field4,0,11)&nbsp;and&nbsp;'+<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;chr(i+63)+'.Field4&nbsp;&gt;&nbsp;'+&nbsp;chr(i+64)+'.Field4'&nbsp;;<br>&nbsp;&nbsp;end&nbsp;;<br><br>&nbsp;&nbsp;sql&nbsp;:=&nbsp;sql1+sql2+sql3;
 
/*<br>表:T1<br>ID,Field1,Field2,Field3,Field4,Field5<br>记录:1,001,'张三','00000001','2007-09-09&nbsp;10:23:24',01<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2,001,'张三','00000001','2007-09-09&nbsp;14:10:34',01<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3,001,'张三','00000001','2007-09-09&nbsp;19:29:34',01<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;...<br><br>写一查询语句查询出这样的结果:<br>&nbsp;&nbsp;&nbsp;&nbsp;Field1,FIeld2,Field3,DT,1,2,3,...16<br>&nbsp;&nbsp;&nbsp;&nbsp;001,张三,00000001,2007-09-09,10:23:24,14:10:34,19:29:34,...<br>&nbsp;&nbsp;&nbsp;&nbsp;...<br><br>里面的1,2,3...16个列为一天内的时间值,最多16列,不足为null<br>其实就是同一个人在一天的所有记录显示在一条记录上&nbsp;<br>*/<br>这样可以看懂吗?帮我看看如何写这个存储过程
 
一看就知道是考勤记录表,哥们,如果想用一个查询语句搞定,估计真是高手级别的(咱目前还没到此级别),不过可以考虑使用function&nbsp;或&nbsp;procedure&nbsp;通过建临时表来处理。
 
同意楼上的,用查询语句有困难,最好用临时表.
 
做了做,发现自己没有用SQl语句直接实现的那个水平,顶&nbsp;dj_lgy&nbsp;[:D]
 
后退
顶部