有一个表 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
函数，参考这个： --生成测试数据 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
--合並函數 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
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
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
我用的是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
f_str是一个用户定义函数，非标准函数；t.commit是为f_str函数返回结果取的别名。Top
还有一个问题当我把这些数据插入到表里面，出现错误： 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
高手都跑到哪里去了，自己顶一下Top
--示例数据 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 未订 未订 --*/
