一复杂存储过程写法！

最新推荐文章于 2021-03-17 17:30:10 发布

lionzl

最新推荐文章于 2021-03-17 17:30:10 发布

阅读量947

点赞数

分类专栏：数据库文章标签：存储 null insert date function table

数据库专栏收录该内容

140 篇文章 2 订阅

订阅专栏

求一复杂存储过程写法！

楼主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 未订未订
--*/