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
where 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, ///a为customer ,engineering对应的不同product分别占一行。以此时的customer engineering为条件,
t.product,sum(y.money)as money // 调用函数,即可得到product.感觉应该有更简单的方式,但是还没想到。只先这样了。
from yieldinfo y left join task t on t.taskid=y.taskid
where 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