求把一函数转换为select语句(老板说今天搞不定,明天要加班.连续8天上班),该怎么解决

求把一函数转换为select语句(老板说今天搞不定,明天要加班..连续8天上班)
在实习.SQL在学校基本是没学过的.然后分配任务要求调高SQL查询语句的速度.原本的语句需要8秒才能查出来
载入一个页面需要几十秒..BOSS要求修改查询语句 不要再调用标量值函数..暗示今天做不了.明天要继续加班.只能求大神搭救
-----------------------------------查询语句是这样的----------------------------------------------------

SQL code

  SELECT  a.ReceiptNO,                                    a.ReceiptDate,                                    a.Sender,                                    a.Receiver,                                    a.Maker,                                    a.Remark,                                    a.LatestReceiveDate,                                    a.TransportationNo,                                    a.DeliveryMan,                                    a.PdtName,                                    a.NumUnit,                                    a.DeclarationNO,                                    b.Name as SenderName,                                    b.ShortName as ShortSenderName,                                    c.Name as ReceiverName,                                    c.ShortName as ShortReceiverName,                                    dbo.getReceiptInvoice(a.ReceiptNO) As InvoiceNos                              FROM tblBizReceiptBill a left join                                     tblCRMManagementUnit b on a.Sender = b.Id left join                                     tblCRMCustomer c on a.Receiver = c.Id  WHERE 1=1 

-------------------------------------调用到标量函数---------------------------------------------------

SQL code

  USE [tcliedms]GO/****** 对象:  UserDefinedFunction [dbo].[getReceiptInvoice]    脚本日期: 04/01/2012 09:28:36 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER   function    [dbo].[getReceiptInvoice](@no nvarchar(50))     RETURNS   varchar(250)     as  begin      declare @strinvoice    nvarchar(250)    declare @invoiceno    nvarchar(50) --发票编号    set @strinvoice = ''         declare cur cursor for     select invoiceno from tblBizReceiptBillDetail where ReceiptNO = @no order by InvoiceNO    open cur    fetch next from cur INTO @invoiceno     while @@fetch_status = 0    begin        set @strinvoice = @strinvoice + ',' + @invoiceno        FETCH NEXT FROM cur INTO @invoiceno    end    close cur    if(len(@strinvoice) > 0)    begin        set @strinvoice = substring(@strinvoice, 2, 250)    end    return   @strinvoice    END

那个发票号的表示类似于这样的
ReceiptNO invoiceno
1 a
1 b
1 c
函数的调用时返回 'abc'

[解决办法]

SQL code

相同条件 多行变一行，不知道是不是你要的declare @tb table (id int, value varchar(10)) insert into @tb values(1, 'aa') insert into @tb values(1, 'bb') insert into @tb values(2, 'aaa') insert into @tb values(2, 'bbb') insert into @tb values(2, 'ccc')select id , [ccname]=stuff((select ' '+[value] from @tb t where id =tv.id for xml path('')), 1, 1, '') from @tb as tvgroup by id /*id    ccname1    aa bb2    aaa bbb ccc*/
[解决办法]


硬扯上的没啥用啊
这做事方式太敷衍了  和老板沟通 用其他方式
解决一时 又解决不了 以后的问题
[解决办法]
探讨

引用:

引用:

SQL code

相同条件 多行变一行，不知道是不是你要的




太感谢了搞定了大部分页面了
最后 一个页面
里面掉用了三个标量值函数
SQL code

[dbo].[getDeclarationInvoice](a.DeclarationNO) As InvoiceNOs,
……


把老板开了
 

 

查看更多 下一篇