SQL 两个表格交互,字符串合并,并且摒除相同的字符串

ERP软件做表格是碰到一部分问题,记下来。

主要是将两个表格并在一起,并且把多行转成一行。

表1-task: taskid,customer,engineering

表2-yieldinfo:yieldid, taskid ,product,yield_num,price,money,yieldtime   ////每个yieldid对应的product列只有一个值

做成查询某一时间段内的:customer,engineering,product,price,money,yield_num,其中product为多行转一行

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

product的多行转一行函数:

ALTER FUNCTION [dbo].[f_str_pro](@cus varchar(200),@eng varchar(200),@starttime varchar(30),@endtime varchar(30))
RETURNS varchar(2000)
AS
BEGIN    
  DECLARE @r varchar(2000)
  SET @r = ‘‘
  SELECT @r=@r + ‘,‘ + a.product      ////多行转一行     
  FROM (select distinct t.customer,t.engineering,y.product
    from yieldinfo y left join task t on t.taskid=y.taskid left join department d on d.depid=y.depid
    where y.audit=1 and y.yield_time>=@starttime and y.yield_time<@endtime)a
    where  a.customer=@cus and a.engineering=@eng
RETURN STUFF(@r, 1, 1, ‘‘)
end

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

被除数为0时,返回0.

isnull(((sum(a.money))/(nullif ((sum(a.yield_num)),0))),0)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~·

SQL查询语句:

 select a.customer,a.engineering,sum(a.yield_num)as yield_num, round(isnull(((sum(a.money))/(nullif ((sum(a.yield_num)),0))),0),0)as price,
 (dbo.f_str_pro(a.customer,a.engineering,‘2013-08-20 11:06:44‘,‘2014-01-20 11:06:44‘)) as product, sum(a.money)as money
 from (select t.customer,t.engineering,sum(y.yield_num)as yield_num,
 round(isnull(((sum(y.money))/(nullif ((sum(y.yield_num)),0))),0),0)as price,
 t.product,sum(y.money)as money
  from yieldinfo y left join task t on t.taskid=y.taskid
  where y.audit=1 and y.yield_time>=‘2013-08-20 11:06:44‘ and y.yield_time<‘2014-01-20 11:06:44‘
   group by t.product,engineering,customer)a
   group by a.engineering,a.customer
   order by customer

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。