=================================================================== --函数dbo.fn_getF create function dbo.fn_getF(@Aid int) returns varchar(500) as begindeclare @str varchar(500)set @str='' select @str=@str+F1 from 表B where AId=@Aid order by BID return(str)end --调用sample select AID,Name,dbo.fn_getF(AID) as F from 表A ================================================================= 看看这个
有点错误return(str)更正为return(@str)
create function dbo.fn_getF(@Aid int) returns varchar(8000) as begindeclare @str varchar(8000)set @str='' select @str=@str+' '+cast(F1 as varchar) from 表B where AId=@Aid set @str=right(@str,len(@str)-1) return(@str) end --调用sample select AID,Name,dbo.fn_getF(AID) F from 表A
set nocount on declare @YourResultTable table (id int, value varchar(10)) insert @YourResultTable values(1, 'cool') insert @YourResultTable values(1, 'nice') insert @YourResultTable values(1, 'wow') insert @YourResultTable values(2, 'cool') insert @YourResultTable values(2, 'wow') insert @YourResultTable values(4, 'nice') insert @YourResultTable values(6, 'cool') insert @YourResultTable values(6, 'nice')--select * from @YourResultTabledeclare @z varchar(100),@q int select @q = 0declare @ProcessTable table (id int, value varchar(100)) insert @ProcessTable select id, value from @YourResultTable order by id, valueupdate @ProcessTable set @z = value = case @q when id then @z else '' end + value + ',' , @q = id from @ProcessTable --select * from @ProcessTableselect id,max(value) from @ProcessTable group by idset nocount off
pengdali(大力)兄 你的方法,在数据量大的时候是否可行? @z很容易溢出
我列一个例子给你看看吧,我的情况跟你一模一样:我感觉还不错,希望帮的上你的忙 源表 jhb jh xh zkpc ---- ---------------- -------------------- ----------- 51 IC006824 .38 13 51 AD007924 .62 14 51 ID058124 3.96 15 目标表 jhk jh ds task 51 双 IC006824(0.38/13) ID058124(3.96/15) AD007924(0.62/14) 说明:我在jhk表中预先设置好了 jh 的记录的如果不预先设置,请你用 insert jhk(jh)(select distinct jh from jhb) 好了,接下来就是存储过程,你自己仔细分析吧,不懂的地方发邮件给我吧,我很少上去,今天是半年后第一次上去呢。 源码:(是SQL的) CREATE PROCEDURE [sp_jhb_jhk] @pjh char(2)='00' AS declare @chmod varchar(10),@xh varchar(18),@nktd varchar(20),@ddate datetime,@nzkpc numeric(10,2) declare @cday char(2),@oldxh varchar(18),@lprint int,@lprint_pre int declare @ctask varchar(200),@i int,@quality char(2),@saverq datetime,@tech_sequ varchar(3) declare @cchmod varchar(10),@cjh char(2),@cxh varchar(18),@cnktd varchar(20),@cdate char(2),@czkpc char(10) ------------------------------------------------------------ START: if @pjh<>'00' goto dataprocess --如果机号传递过来,则处理单一机号 declare curs_jhk cursor for select jh,task from jhk order by jh for update of task ---read only 定义可更新光标[更新列 task ],指向 jhk --------------------------------------------------------- update jhk set task=' ' open curs_jhk fetch curs_jhk into @cjh,@ctask ---读取数据 --print @cjh-----------,@ctask while @@fetch_status=0 ---循环读取, -1表示读取完毕 begin ------------------内循环--依次读取 jhb 表中的记录数***************************************************************** --------------------------------------------------------- dataprocess: ---单个机号处理,加快执行速度 if @pjh<>'00' begin set @cjh=@pjh update jhk set task=' ' where jh=@cjh end declare curs_jhb scroll cursor ---定义具有滚动功能的光标 for select chmod,xh,date,nktd,zkpc,quality,saverq,tech_sequ from jhb where jh=@cjh and finish=0----定义未完成单一机号的光标 order by jh,sequ---在 jhb 表中,按 jh+sequ 排序 for read only ---定义只读光标,指向 jhb -------------------------------------------------------- open curs_jhb ---打开光标 fetch curs_jhb into @chmod,@xh,@ddate,@nktd,@nzkpc,@quality,@saverq,@tech_sequ ---读取参数(读取第一条记录) --- 当天下午所排的数据 if datepart(hour,@saverq)>12 and convert(char(10),@saverq,101)=convert(char(10),getdate(),101) set @lprint=1 ---如果为下午的计划,均要重新排刀 else set @lprint=0 select @oldxh=ltrim(rtrim(@xh)) ---读取型号 select @i=1 select @lprint_pre=1 while @@fetch_status=0 ---循环读取, -1表示读取完毕 begin --------读取参数并转化为字符型 select @cchmod=ltrim(rtrim(@chmod)) ---读取改刀情况 select @cxh=ltrim(rtrim(@xh)) ---读取型号 if @oldxh=@cxh and @i>1 --处理相邻相同的型号 begin select @cxh='' end else select @oldxh=@cxh select @cnktd=ltrim(rtrim(@nktd))---读取纽扣特点 select @i=@i+1 --------------------------------------------------------------------------------- select @czkpc=ltrim(rtrim(convert(varchar(10),@nzkpc))) --读取排产数 ---去掉后缀零 select @czkpc=rtrim(@czkpc) if @czkpc like '%.00' select @czkpc=left(@czkpc,len(@czkpc)-3) if @czkpc like '%._0' select @czkpc=left(@czkpc,len(@czkpc)-1) ---------------------------------------------- select @cdate=ltrim(rtrim(convert(char(2),day(@ddate)))) ---读取日期 ---------------------------------------------------------------------- if @ctask<>' ' begin if @lprint_pre=@lprint and @lprint_pre=1 and datepart(hour,getdate())>12 begin ---- 标记‘||’为备件库设计 select @ctask =ltrim(rtrim(@ctask))+' ||'+@cchmod+ltrim(rtrim(@quality))+@cxh+@cnktd+ case when len(@tech_sequ)>0 then '['+@tech_sequ+']' else '' end+'('+rtrim(@czkpc)+'/'+rtrim(@cdate)+')' select @lprint_pre=0 ---表示已经处理过 end else select @ctask =ltrim(rtrim(@ctask))+' '+@cchmod+ltrim(rtrim(@quality))+@cxh+@cnktd+ case when len(@tech_sequ)>0 then '['+@tech_sequ+']' else '' end+'('+rtrim(@czkpc)+'/'+rtrim(@cdate)+')' end else begin if @lprint_pre=@lprint and @lprint_pre=1 and datepart(hour,getdate())>12 begin ---- 标记‘||’为备件库设计 select @ctask ='||'+@cchmod+ltrim(rtrim(@quality))+@cxh+@cnktd+ case when len(@tech_sequ)>0 then '['+@tech_sequ+']' else '' end+'('+rtrim(@czkpc)+'/'+rtrim(@cdate)+')' select @lprint_pre=0 ---表示已经处理过 end else select @ctask =@cchmod+ltrim(rtrim(@quality))+@cxh+@cnktd+ case when len(@tech_sequ)>0 then '['+@tech_sequ+']' else '' end+'('+rtrim(@czkpc)+'/'+rtrim(@cdate)+')' end update jhk set task =@ctask where jh=@cjh -- where current of curs_jhk IF @@ERROR>0 GOTO ERROR fetch next from curs_jhb into @chmod,@xh,@ddate,@nktd,@nzkpc,@quality,@saverq,@tech_sequ ---读取参数 if datepart(hour,@saverq)>12 set @lprint=1 else set @lprint=0 end --------select * from jhb---------内循环 close curs_jhb --关闭光标jhb deallocate curs_jhb ---删除光标 if @pjh<>'00' ---如果有传递过来的机号,处理完毕即退出 return 1 fetch next from curs_jhk into @cjh,@ctask ---在 jhk 中读取下一条数据 -------------------------*************************************************************************** end deallocate curs_jhk ------------------------------------- RETURN 1 error: close curs_jhb --关闭光标jhb deallocate curs_jhb close curs_jhk --关闭光标jhk deallocate curs_jhk goto start ---跳到开始处重新执行 ------------------------------------- GO
1. build a temp table #a (a_id int) 2. build a cursor for distinct b_id value in the loop of fetching the cursor: use exec ('script') to add b_id value
...sorry, not finished yet, wrong keyborad operation ...1. build a temp table #a (a_id int), 2. populate #a with distinct value of a_id 3. build a cursor for distinct b_id value in the loop of fetching the cursor: a. use exec ('script') to add each b_id value as column name into #a b. insert into #a from table_B where a_id = #a.a_id and b_id = current_cursor_variable 4. select * from #a^^ hope this helps
我也写过类似的查询,是从小票主细表中查出,其中一个列需要保存营业员所销售过的所有柜台,大概10个数据,需要20秒。:(建立一个临时表,然后用的游标循环更新记录。 另外取回细表的行,合并为列写入当前要更新的行,是一个存储过程output 出的,如果你用function 的话,使用table 类型也可以处理。 生成柜台列表的存储过程大概如下: CREATE Proc procGetShopperCounterList(@iYear int,@iMonth int,@ShopperID Varchar(255), @tmpStr varchar(8000) output) as set @tmpStr ='' select DISTINCT '['+m.CounterID+']'+isNull(c.CounterName,'无该柜台') as TmpName into #Tmp_TmpTbl from Gathering_OLD m left join vCounterTbl c on m.CounterID = c.CounterID where (m.isPutupBill=0 and m.isCancel=0 and m.isLargess=0) and (Year(m.OperationDate)=@iYear and Month(m.OperationDate)=@iMonth) and (',' + m.ShopperID + ',' like '%,'+@ShopperID+',%') Update #Tmp_TmpTbl set @tmpStr=case when isNull(@tmpStr,'')='' then isNull(@tmpStr,'')+#Tmp_TmpTbl.TmpName else @tmpStr+','+#Tmp_TmpTbl.TmpName end drop table #Tmp_TmpTbl ---这里是将行数据合并为一个列if Len(ltrim(rtrim(@tmpStr)))=0 set @tmpStr ='无记录' GO
--函数dbo.fn_getF
create function dbo.fn_getF(@Aid int) returns varchar(500)
as
begindeclare @str varchar(500)set @str=''
select @str=@str+F1 from 表B where AId=@Aid order by BID
return(str)end
--调用sample
select AID,Name,dbo.fn_getF(AID) as F from 表A
=================================================================
看看这个
returns varchar(8000)
as
begindeclare @str varchar(8000)set @str=''
select @str=@str+' '+cast(F1 as varchar) from 表B where AId=@Aid
set @str=right(@str,len(@str)-1)
return(@str)
end
--调用sample
select AID,Name,dbo.fn_getF(AID) F from 表A
declare @YourResultTable table (id int, value varchar(10))
insert @YourResultTable values(1, 'cool')
insert @YourResultTable values(1, 'nice')
insert @YourResultTable values(1, 'wow')
insert @YourResultTable values(2, 'cool')
insert @YourResultTable values(2, 'wow')
insert @YourResultTable values(4, 'nice')
insert @YourResultTable values(6, 'cool')
insert @YourResultTable values(6, 'nice')--select * from @YourResultTabledeclare @z varchar(100),@q int
select @q = 0declare @ProcessTable table (id int, value varchar(100))
insert @ProcessTable
select id, value
from @YourResultTable
order by id, valueupdate @ProcessTable
set @z = value = case @q when id then @z else '' end + value +
',' , @q = id
from @ProcessTable
--select * from @ProcessTableselect id,max(value) from @ProcessTable group by idset nocount off
你的方法,在数据量大的时候是否可行?
@z很容易溢出
源表 jhb
jh xh zkpc
---- ---------------- -------------------- -----------
51 IC006824 .38 13
51 AD007924 .62 14
51 ID058124 3.96 15
目标表 jhk
jh ds task
51 双 IC006824(0.38/13) ID058124(3.96/15) AD007924(0.62/14)
说明:我在jhk表中预先设置好了 jh 的记录的如果不预先设置,请你用
insert jhk(jh)(select distinct jh from jhb)
好了,接下来就是存储过程,你自己仔细分析吧,不懂的地方发邮件给我吧,我很少上去,今天是半年后第一次上去呢。
源码:(是SQL的)
CREATE PROCEDURE [sp_jhb_jhk]
@pjh char(2)='00'
AS
declare @chmod varchar(10),@xh varchar(18),@nktd varchar(20),@ddate datetime,@nzkpc numeric(10,2)
declare @cday char(2),@oldxh varchar(18),@lprint int,@lprint_pre int
declare @ctask varchar(200),@i int,@quality char(2),@saverq datetime,@tech_sequ varchar(3)
declare @cchmod varchar(10),@cjh char(2),@cxh varchar(18),@cnktd varchar(20),@cdate char(2),@czkpc char(10)
------------------------------------------------------------
START:
if @pjh<>'00'
goto dataprocess --如果机号传递过来,则处理单一机号
declare curs_jhk cursor
for select jh,task
from jhk
order by jh
for update of task ---read only 定义可更新光标[更新列 task ],指向 jhk
---------------------------------------------------------
update jhk
set task=' '
open curs_jhk
fetch curs_jhk
into @cjh,@ctask ---读取数据
--print @cjh-----------,@ctask
while @@fetch_status=0 ---循环读取, -1表示读取完毕
begin
------------------内循环--依次读取 jhb 表中的记录数*****************************************************************
---------------------------------------------------------
dataprocess: ---单个机号处理,加快执行速度
if @pjh<>'00'
begin
set @cjh=@pjh
update jhk set task=' ' where jh=@cjh
end
declare curs_jhb scroll cursor ---定义具有滚动功能的光标
for select chmod,xh,date,nktd,zkpc,quality,saverq,tech_sequ
from jhb
where jh=@cjh and finish=0----定义未完成单一机号的光标
order by jh,sequ---在 jhb 表中,按 jh+sequ 排序
for read only ---定义只读光标,指向 jhb
--------------------------------------------------------
open curs_jhb ---打开光标
fetch curs_jhb
into @chmod,@xh,@ddate,@nktd,@nzkpc,@quality,@saverq,@tech_sequ ---读取参数(读取第一条记录)
--- 当天下午所排的数据
if datepart(hour,@saverq)>12 and convert(char(10),@saverq,101)=convert(char(10),getdate(),101)
set @lprint=1 ---如果为下午的计划,均要重新排刀
else
set @lprint=0
select @oldxh=ltrim(rtrim(@xh)) ---读取型号
select @i=1
select @lprint_pre=1
while @@fetch_status=0 ---循环读取, -1表示读取完毕
begin
--------读取参数并转化为字符型
select @cchmod=ltrim(rtrim(@chmod)) ---读取改刀情况
select @cxh=ltrim(rtrim(@xh)) ---读取型号
if @oldxh=@cxh and @i>1 --处理相邻相同的型号
begin
select @cxh=''
end
else
select @oldxh=@cxh
select @cnktd=ltrim(rtrim(@nktd))---读取纽扣特点
select @i=@i+1
---------------------------------------------------------------------------------
select @czkpc=ltrim(rtrim(convert(varchar(10),@nzkpc))) --读取排产数
---去掉后缀零
select @czkpc=rtrim(@czkpc)
if @czkpc like '%.00'
select @czkpc=left(@czkpc,len(@czkpc)-3)
if @czkpc like '%._0'
select @czkpc=left(@czkpc,len(@czkpc)-1)
----------------------------------------------
select @cdate=ltrim(rtrim(convert(char(2),day(@ddate)))) ---读取日期
----------------------------------------------------------------------
if @ctask<>' '
begin
if @lprint_pre=@lprint and @lprint_pre=1 and datepart(hour,getdate())>12
begin ---- 标记‘||’为备件库设计
select @ctask =ltrim(rtrim(@ctask))+' ||'+@cchmod+ltrim(rtrim(@quality))+@cxh+@cnktd+
case when len(@tech_sequ)>0 then '['+@tech_sequ+']' else '' end+'('+rtrim(@czkpc)+'/'+rtrim(@cdate)+')'
select @lprint_pre=0 ---表示已经处理过
end
else
select @ctask =ltrim(rtrim(@ctask))+' '+@cchmod+ltrim(rtrim(@quality))+@cxh+@cnktd+
case when len(@tech_sequ)>0 then '['+@tech_sequ+']' else '' end+'('+rtrim(@czkpc)+'/'+rtrim(@cdate)+')'
end
else
begin
if @lprint_pre=@lprint and @lprint_pre=1 and datepart(hour,getdate())>12
begin ---- 标记‘||’为备件库设计
select @ctask ='||'+@cchmod+ltrim(rtrim(@quality))+@cxh+@cnktd+
case when len(@tech_sequ)>0 then '['+@tech_sequ+']' else '' end+'('+rtrim(@czkpc)+'/'+rtrim(@cdate)+')'
select @lprint_pre=0 ---表示已经处理过
end
else
select @ctask =@cchmod+ltrim(rtrim(@quality))+@cxh+@cnktd+
case when len(@tech_sequ)>0 then '['+@tech_sequ+']' else '' end+'('+rtrim(@czkpc)+'/'+rtrim(@cdate)+')'
end update jhk
set task =@ctask
where jh=@cjh
-- where current of curs_jhk
IF @@ERROR>0
GOTO ERROR
fetch next from curs_jhb
into @chmod,@xh,@ddate,@nktd,@nzkpc,@quality,@saverq,@tech_sequ ---读取参数
if datepart(hour,@saverq)>12
set @lprint=1
else
set @lprint=0
end
--------select * from jhb---------内循环
close curs_jhb --关闭光标jhb
deallocate curs_jhb ---删除光标
if @pjh<>'00' ---如果有传递过来的机号,处理完毕即退出
return 1
fetch next from curs_jhk
into @cjh,@ctask ---在 jhk 中读取下一条数据
-------------------------***************************************************************************
end
deallocate curs_jhk
-------------------------------------
RETURN 1
error:
close curs_jhb --关闭光标jhb
deallocate curs_jhb
close curs_jhk --关闭光标jhk
deallocate curs_jhk
goto start ---跳到开始处重新执行
-------------------------------------
GO
2. build a cursor for distinct b_id value
in the loop of fetching the cursor:
use exec ('script') to add b_id value
2. populate #a with distinct value of a_id
3. build a cursor for distinct b_id value
in the loop of fetching the cursor:
a. use exec ('script') to add each b_id value as column name into #a
b. insert into #a from table_B
where a_id = #a.a_id and b_id = current_cursor_variable
4. select * from #a^^ hope this helps
另外取回细表的行,合并为列写入当前要更新的行,是一个存储过程output 出的,如果你用function 的话,使用table 类型也可以处理。
生成柜台列表的存储过程大概如下:
CREATE Proc procGetShopperCounterList(@iYear int,@iMonth int,@ShopperID Varchar(255), @tmpStr varchar(8000) output) as
set @tmpStr ='' select DISTINCT '['+m.CounterID+']'+isNull(c.CounterName,'无该柜台') as TmpName
into #Tmp_TmpTbl
from Gathering_OLD m left join vCounterTbl c on m.CounterID = c.CounterID
where (m.isPutupBill=0 and m.isCancel=0 and m.isLargess=0) and
(Year(m.OperationDate)=@iYear and Month(m.OperationDate)=@iMonth) and
(',' + m.ShopperID + ',' like '%,'+@ShopperID+',%') Update #Tmp_TmpTbl
set @tmpStr=case when isNull(@tmpStr,'')='' then isNull(@tmpStr,'')+#Tmp_TmpTbl.TmpName else @tmpStr+','+#Tmp_TmpTbl.TmpName end
drop table #Tmp_TmpTbl ---这里是将行数据合并为一个列if Len(ltrim(rtrim(@tmpStr)))=0 set @tmpStr ='无记录'
GO