一复杂存储过程写法！

求一复杂存储过程写法！

楼主suppanda007（小宝） 2006-08-28 17:44:07 在 MS-SQL Server / 基础类 提问

有一个表   Table   A   结构如下  
  姓名         颜色1         颜色2             评语  
  name       color1         color2         commint  
  a                 1                   -1                   好  
  b                 2                     1                 不好  
  a                 3                     2                 一般  
  c                 2                     1                   ss  
  a               -1                     2                   aa  
  b                 4                     -1                 bb  
  a                 2                     -1                   cc  
  按照如下规则进行统计：  
  按姓名分组求平均值，如果值为-1就不统计，评语累计  
  统计后结果如下：  
  name       color1         color2         commint  
  a               2                       2                   好,一般,aa,cc  
  b               3                       1                 不好,bb  
  c               2                       1                     ss 问题点数：100、回复次数：7Top

1 楼libin_ftsafe（子陌红尘(TS for Banking Card)）回复于 2006-08-28 17:47:08 得分 10

函数，参考这个：  
   
  --生成测试数据  
  create   table   表(部门   int,人员   varchar(20))  
  insert   into   表   select   1,'张三'  
  insert   into   表   select   1,'李四'  
  insert   into   表   select   1,'王五'  
  insert   into   表   select   2,'赵六'  
  insert   into   表   select   2,'邓七'  
  insert   into   表   select   2,'刘八'  
  go  
   
  --创建用户定义函数  
  create   function   f_str(@department   int)  
  returns   varchar(8000)  
  as  
  begin  
          declare   @ret   varchar(8000)  
          set   @ret   =   ''  
          select   @ret   =   @ret+','+人员   from   表   where   部门   =   @department  
          set   @ret   =   stuff(@ret,1,1,'')  
          return   @ret    
  end  
  go  
   
   
  --执行  
  select   部门,人员=dbo.f_str(部门)   from   表   group   by   部门   order   by   部门  
  go  
   
  --输出结果  
  /*  
  部门     人员  
  ----     --------------  
  1           张三,李四,王五  
  2           赵六,邓七,刘八  
  */  
   
   
  --删除测试数据  
  drop   function   f_str  
  drop   table   表  
  goTop

2 楼WangZWang（先来）回复于 2006-08-28 17:49:07 得分 10

--合並函數  
  create   FUNCTION   uf_HB(@name   varchar(50))  
  RETURNS   varchar(8000)  
  AS  
  BEGIN  
  DECLARE   @r   varchar(8000)  
    SET   @r=''  
        SELECT   @r=@r+','+rtrim(commint)    
        FROM   表   WHERE   name=@name      
    RETURN(stuff(@r,1,1,''))  
  END  
  go  
     
  SELECT   name,color1=sum(color1),color2=sum(color2),[commint]=dbo.uf_HB(name)    
  FROM   表    
  GROUP   BY   name    
  go  
  Top

3 楼libin_ftsafe（子陌红尘(TS for Banking Card)）回复于 2006-08-28 17:54:44 得分 20

create   function   f_str(@name   varchar(10))  
  returns   varchar(20)  
  as  
  begin  
          declare   @str   varchar(20)  
          set   @str=''  
          select   @str=@str+','+commint   from   A   where   name=@name  
          set   @str=stuff(@str,1,1,'')  
          return   @str  
  end  
  go  
   
  select  
          t.name  
          color1/(case   num1   when   0   then   1   else   num1   end),  
          color2/(case   num2   when   0   then   1   else   num2   end),  
          t.commint  
  from  
          (select    
                    name,  
                    sum(case   when   color1!=-1   then   color1   else   0   end)   as   color1,  
                    sum(case   when   color1!=-1   then   1   else   0   end)             as   num1,  
                    sum(case   when   color2!=-1   then   color2   else   0   end)   as   color2,  
                    sum(case   when   color2!=-1   then   1   else   0   end)             as   num2,    
                    dbo.f_str(@name)   as   commint  
            from    
                    A    
            group   by    
                    name)   t  
  order   by  
          t.nameTop

4 楼YiZhiNet（九斤半）回复于 2006-08-28 17:58:56 得分 10

create   table   A(name   char(1),color1   int,color2   int,commint   varchar(20))  
  insert   A  
  select   'a',1,-1,'好'   union   all  
  select   'b',2,1,'不好'   union   all  
  select   'a',3,2,'一般'   union   all  
  select   'c',2,1,'ss'   union   all  
  select   'a',-1,2,'aa'   union   all  
  select   'b',4,-1,'bb'   union   all  
  select   'a',2,-1,'cc'  
  select   *   from   A  
  go  
   
   
  create   function   F_Str(@name   char(1))  
  returns   varchar(250)  
  as  
  begin  
          declare   @sql   varchar(1000)  
          set   @sql=''  
          select   @sql=@sql+','+commint   from   a   where   [name]=@name  
   
          --STUFF   (   character_expression1   ,   start   ,   length   ,character_expression2).    
          --字符串char1从start开始的第length位字符用char2字符串代替  
          return     stuff(@sql,1,1,'')  
  end  
  go  
   
  select    
  name,  
  avg(case   color1   when   -1   then   null   else   color1   end)   as   color1,  
  avg(case   color2   when   -1   then   null   else   color2   end)   as   color1,  
  dbo.F_Str(name)    
  from   A  
  group   by   name  
   
  drop   function   F_Str  
  drop   table   ATop

5 楼suppanda007（小宝）回复于 2006-08-29 09:34:38 得分 0

我用的是db2数据库  
  只用了libin_ftsafe(子陌红尘：当libin告别ftsafe)的这段代码就解决了  
  select  
  t.name,  
  t.id,  
  color/(case   num1   when   0   then   1   else   num1   end)   as   color,  
  color1/(case   num2   when   0   then   1   else   num2   end)   as   color1  
  from  
  (select  
  name,  
  id,  
  sum(case   when   color!=-1   then   color   else   0   end)   as   color,  
  sum(case   when   color!=-1   then   1   else   0   end)   as   num1,  
  sum(case   when   color1!=-1   then   color1   else   0   end)   as   color1,  
  sum(case   when   color1!=-1   then   1   else   0   end)   as   num2  
  from  
  wy_stat_test    
  group   by  
  name,id  
  order   by  
  name  
  )   as   t  
  多谢大家了！  
   
  还有一个疑问，用f_str和t.commit有什么用呢！Top

6 楼libin_ftsafe（子陌红尘(TS for Banking Card)）回复于 2006-08-29 10:55:51 得分 50

f_str是一个用户定义函数，非标准函数；t.commit是为f_str函数返回结果取的别名。Top

7 楼suppanda007（小宝）回复于 2006-08-29 11:35:51 得分 0

还有一个问题当我把这些数据插入到表里面，出现错误：  
  insert   into   result   values(  
  select  
  t.name,  
  t.id,  
  color/(case   num1   when   0   then   1   else   num1   end)   as   color,  
  color1/(case   num2   when   0   then   1   else   num2   end)   as   color1  
  from  
  (select  
  name,  
  id,  
  sum(case   when   color!=-1   then   color   else   0   end)   as   color,  
  sum(case   when   color!=-1   then   1   else   0   end)   as   num1,  
  sum(case   when   color1!=-1   then   color1   else   0   end)   as   color1,  
  sum(case   when   color1!=-1   then   1   else   0   end)   as   num2  
  from  
  wy_stat_test  
  group   by  
  name,id  
  order   by  
  name  
  )   as   t  
  );  
  错误提示：  
  从仅允许一列的子查询中返回了多列，SQLSTATE=42823,请问这是什么错误呢?

有表a(qid,tagname,rid,starttime,endtime,startweekday,endweekday，startdate，enddate)  
  各字段的含义   qid(楼层号）rid（房间编号），tagname(房间的状态），starttime（开始时间）endtime（结束时 间），startweekday（开始星期），endweekday（结束星期），startdate（开始日期），enddate（结束日期），d（为 优先级2比1高）  
  表内容如下：  
  qid   j   rid       tagname       starttime     endtime   startweekday       endweekday     startdate   enddate       d  
  1       1       A1           未订           8：00             9：00             1                             5                   null             null         1  
  1       2       A1           未订           9：00             10：00           1                             5                   null             null         1  
  1       3       A1           未订           10：00         11：00             1                             5                   null             null         1  
  1       4       A1           装修           8：00             9：00           null                       null       2005-1-18   2005-1-19     2    
  1       5       A1           装修           9：00           10：00           null                       null           2005-1-18   2005-1-19   2  
  1       6       A1           装修           10：00         11：00           null                       null           2005-1-18   2005-1-19   2  
  1       7       A2           未订           8：00             9：00             1                             5                   null             null         1  
  1       8       A2           未订           9：00             10：00           1                             5                   null             null         1  
  1       9       A2           未订           10：00         11：00             1                             5                   null             null         1  
  1       10     A2           装修           8：00             9：00           null                       null           2005-1-18   2005-1-19   2  
  1       11     A2           装修           9：00           10：00           null                       null           2005-1-18   2005-1-19   2  
  1       12     A2           装修           10：00         11：00           null                       null           2005-1-18   2005-1-19   2  
  startweekday   ，endweekday代表平常周一到周五时各房间在不同时段的状态。startdate，enddate代表特殊日期各房间各时段的状态。要形成如下的 报表存储过程改如何写（传入的参数为日期如2005-1-17号，为星期一应该显示常规的周一到周五的状态，如果传入的为2005-1-18虽然为星期 二，但我们已特殊日期的优先级高，显示特殊日期时的设置）现在我们传入2005-1-17要显示成如下报表：  
                                        A1                 A2  
  08：00－09：00         未订           未订  
  09：00－10：00         未订           未订  
  11：00－12：00         未订           未订  
  传入2005-1-18时显示如下  
  08：00－09：00         装修           装修  
  09：00－10：00         装修           装修  
  11：00－12：00         装修           装修  
  应用邹建的方法：  
  create   proc   p_qry  
  @date   smalldatetime   --要查询的日期  
  as  
  set   nocount   on  
  declare   @week   int,@s   nvarchar(4000)  
  --格式化日期和得到星期  
  select   @date=convert(char(10),@date,120)  
  ,@week=(@@datefirst+datepart(weekday,@date)-1)%7  
  ,@s=''  
  select   id=identity(int),*   into   #t  
  from(  
  select   top   100   percent  
  qid,rid,tagname,  
  starttime=convert(char(5),starttime,108),  
  endtime=convert(char(5),endtime,108)  
  from   tb  
  where   (@week   between   startweekday   and   endweekday)  
  or(@date   between   startdate   and   enddate)  
  order   by   qid,rid,starttime,d   desc)a  
   
  select   @s=@s+N',['+rtrim(rid)  
  +N']=max(case   when   qid='+rtrim(qid)  
  +N'   and   rid=N'''+rtrim(rid)  
  +N'''   then   tagname   else   N''''   end)'  
  from   #t   group   by   qid,rid  
  exec('  
  select   starttime,endtime'+@s+'    
  from   #t   a  
  where   not   exists(  
  select   *   from   #t  
  where   qid=a.qid   and   rid=a.rid    
  and   starttime=a.starttime  
  and   endtime=a.endtime  
  and   id<a.id)  
  group   by   starttime,endtime')  
  已经达到我要的目的了。但我还有一张表c是记录预定信息的表如下：  
  id     qid       rid             consumedate         j  
  1         1           A1               2005-1-17           1  
  1         1           A2               2005-1-17           2  
  （qid属于那楼、rid哪个房间、consumedate   订的是哪个一天的，j（与a表的j）标识是订的是那个时段的  
  a表为为初使设置房间的在不同时间的状态。如我要输入日期2005－1－17号显示如下  
                                        A1                 A2  
  08：00－09：00         被订           未订  
  09：00－10：00         未订           被订  
  11：00－12：00         未订           未订  
  该如何写这样的存储过程呢？ 问题点数：50、回复次数：2Top

1 楼waiking33（waiking_wei）回复于 2005-01-20 17:19:40 得分 0

高手都跑到哪里去了，自己顶一下Top

2 楼zjcxc（邹建）回复于 2005-01-21 10:05:35 得分 50

--示例数据  
  create   table   a(  
  qid   int,j   int,rid   nvarchar(10),tagname   nvarchar(10),  
  starttime   smalldatetime,endtime   smalldatetime,  
  startweekday   int,endweekday   int,  
  startdate   smalldatetime,enddate   smalldatetime,d   int)  
  insert   a     select   1,1   ,'A1','未订','08:00','09:00',1       ,5       ,null               ,null               ,1  
  union   all   select   1,2   ,'A1','未订','09:00','10:00',1       ,5       ,null               ,null               ,1  
  union   all   select   1,3   ,'A1','未订','10:00','11:00',1       ,5       ,null               ,null               ,1  
  union   all   select   1,4   ,'A1','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2    
  union   all   select   1,5   ,'A1','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2  
  union   all   select   1,6   ,'A1','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2  
  union   all   select   1,7   ,'A2','未订','08:00','09:00',1       ,5       ,null               ,null               ,1  
  union   all   select   1,8   ,'A2','未订','09:00','10:00',1       ,5       ,null               ,null               ,1  
  union   all   select   1,9   ,'A2','未订','10:00','11:00',1       ,5       ,null               ,null               ,1  
  union   all   select   1,10,'A2','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2  
  union   all   select   1,11,'A2','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2  
  union   all   select   1,12,'A2','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2  
   
  create   table   c(id   int,qid   int,rid   varchar(10),consumedate   smalldatetime,j   int)  
  insert   c     select   1,1,'A1','2005-1-17',1  
  union   all   select   1,1,'A2','2005-1-17',8  
  go  
   
  create   proc   p_qry  
  @date   smalldatetime   --要查询的日期  
  as  
  set   nocount   on  
  declare   @week   int,@s   nvarchar(4000)  
  --格式化日期和得到星期  
  select   @date=convert(char(10),@date,120)  
  ,@week=(@@datefirst+datepart(weekday,@date)-1)%7  
  ,@s=''  
  select   id=identity(int),*   into   #t  
  from(  
  select   top   100   percent  
  a.qid,a.rid,a.j,  
  tagname=case   when   c.id   is   null   then   a.tagname   else   N'被订'   end,  
  starttime=convert(char(5),a.starttime,108),  
  endtime=convert(char(5),a.endtime,108)  
  from   a  
  left   join   c   on   a.qid=c.qid   and   a.rid=c.rid   and   a.j=c.j  
  and   c.consumedate=@date  
  where   (@week   between   a.startweekday   and   a.endweekday)  
  or(@date   between   a.startdate   and   a.enddate)  
  order   by   a.qid,a.rid,a.starttime,a.d   desc)a  
   
  select   @s=@s+N',['+rtrim(rid)  
  +N']=max(case   when   qid='+rtrim(qid)  
  +N'   and   rid=N'''+rtrim(rid)  
  +N'''   then   tagname   else   N''''   end)'  
  from   #t   group   by   qid,rid  
  exec('  
  select   starttime,endtime'+@s+'    
  from   #t   a  
  where   not   exists(  
  select   *   from   #t  
  where   qid=a.qid   and   rid=a.rid    
  and   starttime=a.starttime  
  and   endtime=a.endtime  
  and   id<a.id)  
  group   by   starttime,endtime')  
  go  
   
  --调用  
  exec   p_qry   '2005-1-17'  
  go  
   
  --删除测试  
  drop   table   a,c  
  drop   proc   p_qry  
   
  /*&/--测试结果  
   
  starttime   endtime   A1                   A2                    
  ---------   -------   ----------   ----------    
  08:00           09:00       被订                   未订  
  09:00           10:00       未订                   被订  
  10:00           11:00       未订                   未订  
  --*/