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

Question

在实习.SQL在学校基本是没学过的.然后分配任务要求调高SQL查询语句的速度.原本的语句需要8秒才能查出来
载入一个页面需要几十秒..BOSS要求修改查询语句 不要再调用标量值函数..暗示今天做不了.明天要继续加班.只能求大神搭救
-----------------------------------查询语句是这样的----------------------------------------------------  SELECT  a.ReceiptNO,
                                    a.ReceiptDate,
                                    a.Sender,
                                    a.Receiver,
                                    a.Maker,
                                    a.Re,
                                    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 
-------------------------------------调用到标量函数---------------------------------------------------  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'

Answer 1

你原来的是使用游标，你找一下字符串拼接的sql，网上很多的，

Answer 2

相同条件 多行变一行，不知道是不是你要的
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*/

Answer 3

XML 很神奇，建议参考楼上的方法

Answer 4

太感谢了搞定了大部分页面了
最后 一个页面
里面掉用了三个标量值函数  [dbo].[getDeclarationInvoice](a.DeclarationNO) As InvoiceNOs,                            
   [dbo].[getDeclarationSupplier](a.DeclarationNO) As Suppliers,                            
   [dbo].[getDeclarationConsignee](a.DeclarationNO) As Consignees,                差不多三个函数都类属于USE [tcliedms]
GO
/****** 对象:  UserDefinedFunction [dbo].[getDeclarationSupplier]    脚本日期: 04/01/2012 12:02:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER   function    [dbo].[getDeclarationSupplier](@no nvarchar(50))   
  RETURNS   varchar(250)   
  as  
begin  
declare @strsupplier nvarchar(250)
declare @supplier nvarchar(50)
declare cur cursor for select distinct c.shortname from tblBizImportDeclarationInvoice a, tblBizImportInvoice b, tblCRMSupplier c,  tblBizImportContract d
where a.InvoiceNO = b.InvoiceNO AND b.ImportCN = d.CN AND d.Seller = c.ID AND a.DeclarationNO = @no
set @strsupplier = '' 
open cur
fetch next from cur INTO @supplier while @@fetch_status = 0
begin
set @strsupplier = @strsupplier + ', ' + @supplier
FETCH NEXT FROM cur INTO @supplier
end
close cur
if(len(@strsupplier) > 0)
begin
set @strsupplier = substring(@strsupplier, 2, 250)
end
return   @strsupplier   
END
                
-----------------------------------------------------------------------------------------
我把每条函数都改成  stuff((select ' '+[value] from @tb t where id =tv.id for xml path('')), 1, 1, '') 但还是很慢很慢.有时候还显示磁盘空间不足..

Answer 5

给楼主参考：1.几个表的数据量多少？表有没有主键？
2.查询结果返回多少行记录？，如果是返回数据行数比较多，可考虑在数据库后台通过存储过程分页解决。
3.有没有查看执行计划？执行计划中性能最差的在哪位置？从考虑索引入手，可参考SQL Server 提供的优化工具[Database Engine Tuning Advisor] 即‘数据库引擎优化顾问’

Answer 6

  
/*
查询时调用函数 数据量一大.效率确实不行
建议 楼主 先暂时用xml
或者直接再加一个表.用触发器或者job实现修改数据
ReceiptNO invoiceno
1 a,b,c
2 b,n,m*/