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