日期:2014-05-18  浏览次数:20331 次

求把一函数转换为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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   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 tv
group by id 
/*
id    ccname
1    aa bb
2    aaa bbb ccc*/

------解决方案--------------------


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

引用:

SQL code

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




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

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


把老板开了